<?php
/**
 * Copyright (c) 2011 Jakob Sack <mail@jakobsack.de>
 * Copyright (c) 2012 Bart Visscher <bartv@thisnet.nl>
 * Copyright (c) 2012 Georg Ehrke <ownclouddev at georgswebsite dot de>
 * This file is licensed under the Affero General Public License version 3 or
 * later.
 * See the COPYING-README file.
 */

/**
 * This class manages our calendar objects
 */

require_once(OC_App::getAppPath('resumes') . "/lib/share/share.php");
JLoader::register("PResumesHelper", OC::$SERVERROOT."/apps/resumes/lib/helper.php");

class PCalendarModelObject extends JModelLegacy
{
   static $validRejectReasons = array("希望更改其他时间", "已有其他更好的机会","对该职位已无兴趣","不再打算换工作","其他原因放弃");

   /**
    * @brief Returns all objects of a calendar
    * @param integer $id
    * @return array
    *
    * The objects are associative arrays. You'll find the original vObject in
    * ['calendardata']
    */
   public function all($id) {
      $db = PFactory::getDbo();
      $query = "SELECT * FROM *PREFIX*calendar_objects WHERE calendarid = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $calendarobjects = $db->loadAssocList();

      return $calendarobjects;
   }
    
   public function getObjectNumber($uid){
      if(empty($uid)) return false;
      $db = PFactory::getDbo(); 
      $query = "SELECT num_calendarevents FROM #__jobs_employer WHERE user_id = ".intval($uid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $objectsnumber = $db->loadResult();
      return $objectsnumber;
   }

   /**
    * @brief Returns all objects of a calendar between $start and $end
    * @param integer $id
    * @param DateTime $start
    * @param DateTime $end
    * @param integer $jobid jobid
    * @param string $share share key to access one event
    * @return array
    *
    * The objects are associative arrays. You'll find the original vObject
    * in ['calendardata']
    */
   public function allInPeriod($id, $start, $end, $jobid=0, $share='') {
      $db = PFactory::getDbo();
      $start = $this->getUTCforMDB($start);
      $end = $this->getUTCforMDB($end);
      // Get share event
      if ( !empty($share) ) {
         $query = "SELECT a.*,od.result,od.offer_stat
            FROM *PREFIX*calendar_objects a
            LEFT JOIN *PREFIX*calendar_interview_events b ON b.parent = a.id
            LEFT JOIN *PREFIX*calendar_objects_details od ON od.objectid = b.parent
            LEFT JOIN *PREFIX*share d ON d.share_with = b.uid
            WHERE d.token = ".$db->quote($share)." AND d.item_type ='event' AND b.objectid = d.item_source 
            AND IF(a.objecttype='VEVENT',od.result<>5,od.offer_stat<>2)
            AND ((a.startdate >= ".$db->quote($start)." AND a.startdate <= ".$db->quote($end)." AND a.repeating = 0)
            OR (a.enddate >= ".$db->quote($start)." AND a.enddate <= ".$db->quote($end)." AND a.repeating = 0)
            OR (a.startdate <= ".$db->quote($end)." AND a.repeating = 1))";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $calendarobjects = $db->loadAssocList();
         return $calendarobjects;
      }

      // Not a share event, get login user id and master id for permission check
      $userid = OC_USER::getUser();
      $master_uid = PFactory::getMasterUser()->get("id");
      /**
       * $query help
       * 1. Select "ie.parent, ie.uid" is for HAVING use.
       * 2. Putting ORDER BY b.id DESC in subquery will filter out the right objectid for login user, not always the master objectid.
       * 3. For master user, " HAVING r.parent=r.id " is enough, because it can see every events.
       * 4. For subaccounts, we should add "GROUP BY r.parent" first, to deal with duplicated objects.
       * 5. For subaccounts, we should add "OR (r.uid = ".$db->quote($userid)." AND r.parent <> r.id)" to add back what we are missing.
       *    (such as: A share event for jobid1, but I don't have the permission to manage job1).
       * 6. Use ie.jobid to search events under jobid. Don't use od.jobid. (This database change is already in table_3.2to3.3.sql).
       *
       * @var string
       */
      $query = "SELECT * FROM (
         SELECT DISTINCT b.*, ie.parent, ie.uid,od.result,od.offer_stat
         FROM *PREFIX*calendar_objects b
         LEFT JOIN *PREFIX*calendar_calendars c ON c.id = b.calendarid
         LEFT JOIN *PREFIX*calendar_interview_events ie ON ie.objectid = b.id
         LEFT JOIN *PREFIX*calendar_objects_details od ON od.objectid = ie.parent
         LEFT JOIN #__jobs_job jj ON jj.id = ie.jobid" .
         ( PFactory::isMasterUser() ? " LEFT JOIN *PREFIX*users_extended ux ON ux.uid = c.userid " : "" ) .
         ( PFactory::isMasterUser() ? "" : " LEFT JOIN *PREFIX*hr_jobmanager jm ON jm.jobid = od.jobid" ) .
         ( PFactory::isMasterUser() ? " WHERE jj.employer_id = ".intval($master_uid)." AND ie.parent = od.objectid AND " : " WHERE " ) .
         ( PFactory::isMasterUser() ? " ( c.userid = ".intval($userid)." OR ux.owner = ".intval($userid)." )" : " ( c.userid = ".$db->quote($userid)." OR jm.manager = ".$db->quote($userid).")" ) .
         " AND ((b.startdate >= ".$db->quote($start)." AND b.startdate <= ".$db->quote($end)." AND b.repeating = 0)
         OR (b.enddate >= ".$db->quote($start)." AND b.enddate <= ".$db->quote($end)." AND b.repeating = 0)
         OR (b.startdate <= ".$db->quote($end)." AND b.repeating = 1)) AND IF(b.objecttype='VEVENT',od.result<>5,od.offer_stat<>2) " .
         ( intval($jobid) > 0 ? " AND ie.jobid = ".intval($jobid) : "" ).
         " ORDER BY b.startdate ASC ) AS r ".
         ( PFactory::isMasterUser() ? " HAVING r.parent=r.id " : " GROUP BY r.parent HAVING r.parent=r.id OR (r.uid = ".$db->quote($userid)." AND r.parent <> r.id) " );

      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // get cached data now
      $calendarobjects = $cache->call(array($this, '_allInPeriod'), $query);
      // Event Driven Cache/Object Caching
      CacheHelper::setTriggers(PFactory::getMasterUser()->get("id"), $cache, array('*PREFIX*calendar_objects','*PREFIX*calendar_interview_events','*PREFIX*calendar_objects_details','*PREFIX*calendar_calendars','*PREFIX*users_extended','*PREFIX*hr_jobmanager','#__jobs_job'));

      return $calendarobjects;
   }

   public function _allInPeriod($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }

   /**
    * @brief Returns an object
    * @param integer $id
    * @return associative array
    */
   public function find($id) {
      $db = PFactory::getDbo();
      $query = "SELECT * FROM *PREFIX*calendar_objects WHERE id = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadAssoc();

      return $result;
   }

   /**
    * @brief finds an object by its DAV Data
    * @param integer $cid Calendar id
    * @param string $uri the uri ('filename')
    * @return associative array
    */
   public function findWhereDAVDataIs($cid,$uri) {
      $db = PFactory::getDbo();
      $query = "SELECT * FROM *PREFIX*calendar_objects WHERE calendarid = ".intval($cid)." AND uri = ".$db->quote($uri);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadAssoc();

      return $result;
   }

   /**
    * @brief Adds an object
    * @param integer $id Calendar id
    * @param string $data  object
    * @return insertid
    */
   public function add($id,$data,$post) {
      $db = PFactory::getDbo();
      // we need to stay on master on connection in duration of adding 
      $db->stayOnMaster(); 

      // Add Master(parent) Object
      $object_id = $this->addObject($id,$data);

      //status_id: 210='安排面试',240="复试"
      if($post['round'] == 0){
         $status_id = PCalendarHelper::JOBSEEKER_STATUS_INTERVIEW;
      }else{
         $status_id = PCalendarHelper::JOBSEEKER_STATUS_RETEST;
      }
      

      // Extra table inserts
      $round = isset($post['round']) ? $post['round']+1 : 1;
      $current_objectid = isset($post['current_objectid']) ? $post['current_objectid'] : 0;
      $source = isset($post['source']) ? $post['source'] : '';
      $is_phone = isset($post['isphone']) ? 1 : 0;
      $sendsms = isset($post['sendsms']) ? 1: 0;
      $config = PFactory::getConfig();
      $timezone = $config->get('offset', 'Asia/Shanghai');
      $creator_id = OC_USER::getUser();
      $creator_name = OC_USER::getDisplayName();
      // Pre-operations for interviewers
      $interviewers = JRequest::getVar('interviewer', array());
      $starttime = JRequest::getVar('starttime', array());
      $endtime = JRequest::getVar('endtime', array());
      if((count($interviewers) != count($starttime)) || (count($starttime) != count($endtime))){
         $this->setError("请检查面试官时间！");
         return false;
      }
      if(empty($post['jobseekerid'])){
         $this->setError("网络异常，请刷新页面重试！");
         return false;
      }
      if(empty($post['address'])){
         $this->setError("请输入面试地点！");
         return false;
      }
      if(empty($post['contact_phone'])){
         $this->setError("请输入联系电话！");
         return false;
      } else if(!JobHelper::isPhone($post['contact_phone'])) {
         $this->setError("请输入正确的联系电话！");
         return false;
      }
      if(!empty($post['contact'])&&!preg_match('/^(?!\d+$)[^\n]*$/', $post['contact'])) {
         $this->setError("联系人不能为纯数字！");
         return false;
      }
      $interviewer_data = array();
      $jobSeekerEmail = PResumesHelper::getJobSeekerEmail($post['jobseekerid']);
      foreach ($interviewers as $k => $v) {
         if (!empty($v)) {
            if($jobSeekerEmail == $v) {
               $this->setError("面试官邮箱不能与应聘者邮箱相同！");
               return false;
            }
            $starttime[$k] =  PFactory::getDate($post['from'].$starttime[$k].':00')->format('Y-m-d H:i:s');
            $endtime[$k] =  PFactory::getDate($post['from'].$endtime[$k].':00')->format('Y-m-d H:i:s');
            $interviewer_data[] = array(
               'uid' => $v,
               'accessclass' => $post['accessclass'],
               'objecttype' => $post['objecttype'],
               'jobseekername' => $post['jobseekername'],
               'jobtitle' => $post['jobtitle'],
               'start_time' => $starttime[$k],
               'end_time' => $endtime[$k]
            );
         }
      }
      $numinterviewers = count($interviewer_data);
      if($numinterviewers <= 0){
         $this->setError("请通知至少一位面试官！");
         return false;
      }
      // Insert master's oc_calendars_interview_result
      // Generate reject token just the first time, one token pairs one jobseekerid
      $r_token = md5(time() . $post['jobseekerid'] . rand(99,780));
      $query = "INSERT INTO *PREFIX*calendar_interview_result
         (`jobseekerid`, `jobid`, `totalinterviewers`, `last_interview_objectid`, `token`)
         VALUES (".intval($post['jobseekerid']).", ".intval($post['jobid']).", ".intval($numinterviewers).", ".intval($object_id).", ".$db->quote($r_token).")
         ON DUPLICATE KEY UPDATE jobid = ".intval($post['jobid']).", last_interview_objectid = ".intval($object_id) . ",
                                 totalinterviewers = totalinterviewers+".intval($numinterviewers).", canceled = 0";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      }

      // Insert master's oc_calendars_interview_events
      $query = "INSERT INTO *PREFIX*calendar_interview_events
         (`parent`, `next`, `objectid`, `eventtype`, `jobid`, `uid`, `displayname`, `jobseekerid`, `jobseekername`)
         VALUES (".intval($object_id).", 0, ".intval($object_id).", ".$db->quote($post['objecttype']).", ".intval($post['jobid']).", ".$db->quote($creator_id).", ".$db->quote($creator_name).", ".intval($post['jobseekerid']).", ".$db->quote($post['jobseekername']).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      }

      // Insert master oc_calendar_objects_details
      $query = "INSERT INTO *PREFIX*calendar_objects_details
                (`objectid`, `jobid`, `summary`, `is_phone`, `notify_text`, `round`, `numinterviewers`, `attachment`, `contact`, `contact_phone`, `address`)
                VALUES (".intval($object_id).", ".intval($post['jobid']).", ".$db->quote($post['summary']).", ".$is_phone.", ".$db->quote($post['notify_text']).", ".$round.", ".intval($numinterviewers).", ".intval($post['attachment']).", ".$db->quote($post['contact']).", ".$db->quote($post['contact_phone']).", ".$db->quote($post['address']).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      }else{
         $detailsid = $db->insertid();
         PCalendarHelper::updateNumEvent();
      }

      /**
       * Action for oc_calendars_interview_events
       * If not first round event, update last master object's `next` field
       * We need the latest objectid ($current_objectid)
       * If the new event is round 3, we may need event round 2's objectid
       */
      if ( $round > 1 || $current_objectid > 0 ) {
         if ( !empty($current_objectid) ) {
            $query = "UPDATE *PREFIX*calendar_interview_events SET next = ".intval($object_id)." WHERE objectid =".intval($current_objectid);
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if (!$db->execute()) {
               $db->leaveOnMaster();
               $this->setError("网络异常，请重试！");
               return false;
            }
         }
         // when add an event set result = 3 "复试" if round>1
         $query = "UPDATE *PREFIX*calendar_objects_details SET result = ".intval(PCalendarHelper::INTERVIEW_RESULT_RETEST)." WHERE id =".intval($detailsid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $db->leaveOnMaster();
            $this->setError("网络异常，请重试！");
            return false;
         }
      }
      $calendar_model = new PCalendarModelCalendar();
      // Creat calenders(if dont have one) and objects for interviewers
      foreach ($interviewer_data  as $k => $each_data) {
         // Find sub calendar id
         $sub_calender = $calendar_model->findByUid($each_data['uid']);
         if ($sub_calender) {
            $sub_calenderid = $sub_calender['id'];
         } else {
            // Creat sub calender
            $sub_calenderid = $calendar_model->addCalendar($each_data['uid'],"Interviewer",'VEVENT,VTODO,VJOURNAL',$timezone);
         }
         $interviewer_data[$k]['calender_id'] = $sub_calenderid;
         // Create sub Vcanlendar
         $sub_vcal = $this->createInterviewerVCalendar($each_data);
         // Add sub object
         $sub_objectid = $this->addObject($sub_calenderid,$sub_vcal->serialize(),'sub');
         $interviewer_data[$k]['object_id'] = $sub_objectid;
      }

      // Insert interviewer's oc_calendars_interview_events
      $value_arr = array();
      foreach ($interviewer_data as $k => $v) {
         $displayname = PCalendarHelper::getDisplayName($v['uid']);
         $value_arr[] = "(".intval($object_id).", 0, ".intval($v['object_id']).", ".$db->quote($v['objecttype']).", ".intval($post['jobid']).", ".$db->quote($v['uid']).", ".$db->quote($displayname).", ".intval($post['jobseekerid']).", ".$db->quote($v['jobseekername']).")";
      }
      $values = implode(",", $value_arr);
      $query = "INSERT INTO *PREFIX*calendar_interview_events
         (`parent`, `next`, `objectid`, `eventtype`, `jobid`, `uid`, `displayname`, `jobseekerid`, `jobseekername`)
         VALUES ".$values;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      }

      foreach ($interviewer_data as $k => $v) {
         $emailArr[] = $v['uid'];
      }
      $sharedlist = ResumeShare::getShareWithList($post['jobseekerid'], $emailArr);
      $sharedlist = empty($sharedlist)?array():$sharedlist;
      $notsharedlist = array_values(array_unique(array_diff($emailArr, $sharedlist)));
      // Add share events to oc_share
      foreach ($interviewer_data as $k => $v) {
         $event_share_token = OCP\Share::shareItem('event', $v['object_id'], OCP\Share::SHARE_TYPE_LINK, $v['uid'], \OCP\PERMISSION_READ);
         if (in_array($v['uid'],$notsharedlist)) {
               $resume_share_token = OCP\Share::shareItem('resumes', $post['jobseekerid'], OCP\Share::SHARE_TYPE_LINK, $v['uid'], \OCP\PERMISSION_READ|\OCP\PERMISSION_SHARE);
         }
      }

      // Notify interviewer to attend this event (new)
      $email_users = PCalendarHelper::getInterviewerEmailDataFromParentid($object_id);
      foreach ($email_users as $k => $user) {
         PCalendarHelper::sendInterviewerEventEmail($user, 'add');
      }

      // Notify jobseeker to attend this event
      $this->notifyJobseeker($object_id, 'add',$sendsms);

      // leave on master now 
      $db->leaveOnMaster();
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects', '*PREFIX*calendar_interview_events', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result', '*PREFIX*share'));

      //如果创建下一轮面试,创建完新一轮面试后把状态改为 --
      $query = "UPDATE *PREFIX*calendar_objects_details SET result=0 WHERE objectid=" . intval($object_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();


      if(!empty($post['jobseekerid'])){
         if (!PResumesHelper::setResumeStatus($post['jobseekerid'],$status_id)) {
            $db->leaveOnMaster();
            $this->setError("网络异常，请重试！");
            return false;
         }
      }

      return $object_id;
   }

   /**
    * @brief Adds an object (sub function)
    * @param integer $id Calendar id
    * @param object $data  object
    * @param string $type  string
    * @return insertid
    */
   public function addObject($id,$data,$type=null) {
      $calendar_model = new PCalendarModelCalendar();
      $app_model = new PCalendarModelApp();

      if ($type != "sub") {
         $calendar = $calendar_model->find($id);
         if ($calendar['userid'] != OC_USER::getUser()) {
            $sharedCalendar = OCP\Share::getItemSharedWithBySource('calendar', $id);
            if (!$sharedCalendar || !($sharedCalendar['permissions'] & OCP\PERMISSION_CREATE)) {
               $this->setError("你没有权限添加日程到此日历!");
               return false;
            }
         }
      }

      $object = OC_VObject::parse($data);
      list($objecttype,$startdate,$enddate,$summary,$repeating,$uid) = $this->extractData($object);

      if(is_null($uid)) {
         $object->setUID();
         $data = $object->serialize();
      }

      $uri = 'Pipahr-'.md5($data.rand().time()).'.ics';

      $db = PFactory::getDbo();
      $query = "INSERT INTO *PREFIX*calendar_objects (`calendarid`,`objecttype`,`startdate`,`enddate`,`repeating`,`summary`,`calendardata`,`uri`,`lastmodified`) VALUES (".intval($id).",".$db->quote($objecttype).",".$db->quote($startdate).",".$db->quote($enddate).",".intval($repeating).",".$db->quote($summary).",'".$data."',".$db->quote($uri).",".time().")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError("网络异常，请重试！");
         return false;
      } else {
         $object_id = $db->insertid();
      }

      $app_model->loadCategoriesFromVCalendar($object_id, $object);

      $calendar_model->touchCalendar($id); // Update calendar ctag
      OCP\Util::emitHook('OC_Calendar', 'addEvent', $object_id);
      return $object_id;
   }

   /**
    * @brief Adds an object with the data provided by sabredav
    * @param integer $id Calendar id
    * @param string $uri   the uri the card will have
    * @param string $data  object
    * @return insertid
    */
   public function addFromDAVData($id,$uri,$data) {
      $db = PFactory::getDbo();
      // we need to stay on master on connection in duration of adding 
      $db->stayOnMaster(); 

      $calendar_model = new PCalendarModelCalendar();
      $calendar = $calendar_model->find($id);
      if ($calendar['userid'] != OC_USER::getUser()) {
         $sharedCalendar = OCP\Share::getItemSharedWithBySource('calendar', $id);
         if (!$sharedCalendar || !($sharedCalendar['permissions'] & OCP\PERMISSION_CREATE)) {
            $db->leaveOnMaster();
            $this->setError("你没有权限添加日程到此日历!");
            return false;
         }
      }
      $object = OC_VObject::parse($data);
      list($type,$startdate,$enddate,$summary,$repeating,$uid) = $this->extractData($object);

      $query = "INSERT INTO *PREFIX*calendar_objects (`calendarid`,`objecttype`,`startdate`,`enddate`,`repeating`,`summary`,`calendardata`,`uri`,`lastmodified`) VALUES (".intval($id).",".$db->quote($type).",".$db->quote($startdate).",".$db->quote($enddate).",".intval($repeating).",".$db->quote($summary).",'".$data."',".$db->quote($uri).",".time().")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      } else {
         $object_id = $db->insertid();
      }

      $calendar_model->touchCalendar($id);
      OCP\Util::emitHook('OC_Calendar', 'addEvent', $object_id);

      // leave on master now 
      $db->leaveOnMaster();

      return $object_id;
   }

   /**
    * @brief edits an object (VEVENT)
    * @param integer $id id of object (master object)
    * @param string $data  object
    * @return boolean
    */
   public function edit($id, $data, $post, &$jobseekeremailsent=false, &$intervieweremailsent=false) {
      $db = PFactory::getDbo();
      // we need to stay on master on connection in duration of adding 
      $db->stayOnMaster(); 

      $force_edit = $allowedit = $this->checkObjectEditPermission($id);
      if ( !$allowedit ) {
         $db->leaveOnMaster();
         $this->setError("您没有管理该面试日程的权限！");
         return false;
      }
      // Extra data and interviewer object edit
      $is_phone = isset($post['isphone']) ? 1 : 0;
      $config = PFactory::getConfig();
      $timezone = $config->get('offset', 'Asia/Shanghai');
      $object_id = $id; // this is master's object id
      $interview_form_type = JRequest::getInt('interview_form_type',0);
      $jobseekerid = JRequest::getInt('jobseekerid',0);
      $round = JRequest::getInt('round',0);
      $resultid = JRequest::getInt('resultid',0);

      // Check startdate is changed
      $date_changed = $this->checkDateChange($id, $data);
      $jobseeker_data_changed = $this->checkJobseekerDataChange($id, $post, $summaryChanged);

      //result表中totalfeedbacks不是全部复位到0,而是减去当前details表中的totalfeedbacks
      if($interview_form_type == PCalendarHelper::INTERVIEW_FORM_RESET || $interview_form_type == PCalendarHelper::INTERVIEW_FORM_EDIT){
         $query1 ="UPDATE *PREFIX*calendar_interview_result AS cir
                        LEFT JOIN *PREFIX*calendar_objects_details AS cod ON cod.objectid = cir.last_interview_objectid
                        SET cir.totalfeedbacks = (cir.totalfeedbacks - cod.totalfeedbacks) WHERE cir.last_interview_objectid = " . intval($object_id);
         $db->setQuery(PUtil::replaceOCPrefix($query1));
         if(!$db->execute()){
            return false;

         }
      }

      // Update master's vcalendar
      $this->editObject($id, $data, $force_edit);

      

      // Get old interviewers
      if ($resultid == PCalendarHelper::INTERVIEW_RESULT_ABSENT) {
         $query = "SELECT cie.objectid, cie.uid, co.startdate,co.enddate,cod.summary,cod.is_phone,cod.attachment FROM *PREFIX*calendar_interview_events cie LEFT JOIN *PREFIX*calendar_objects co ON cie.objectid = co.id LEFT JOIN *PREFIX*calendar_objects_details cod ON cie.parent = cod.objectid LEFT JOIN oc_calendar_feedback_answer AS fa ON fa.objectid = cie.objectid WHERE cie.parent = ".intval($object_id)." AND cie.objectid <>".intval($object_id)." AND fa.id IS NULL ORDER BY co.startdate ASC";
         
         //如果hr设置缺席并且重新编辑面试,对于未反馈过的反馈表,event表中的新加字段absent_flag设置为1
         $update_absent_flag = "UPDATE *PREFIX*calendar_interview_events cie 
                                    LEFT JOIN *PREFIX*calendar_objects co ON cie.objectid = co.id 
                                    LEFT JOIN *PREFIX*calendar_objects_details cod ON cie.parent = cod.objectid 
                                    LEFT JOIN oc_calendar_feedback_answer AS fa ON fa.objectid = cie.objectid 
                                    SET cie.absent_flag = 1
                                    WHERE cie.parent = ".intval($object_id)." 
                                       AND cie.objectid <>".intval($object_id)." 
                                       AND fa.id IS NOT NULL";
         $db->setQuery(PUtil::replaceOCPrefix($update_absent_flag));
         if(!$db->execute()) {
            $db->leaveOnMaster();
            $this->setError("网络异常，请重试！");
            return false;
         }
      } else {
         $query = "SELECT cie.objectid, cie.uid, co.startdate,co.enddate,cod.summary,cod.is_phone,cod.attachment FROM *PREFIX*calendar_interview_events cie LEFT JOIN *PREFIX*calendar_objects co ON cie.objectid = co.id LEFT JOIN *PREFIX*calendar_objects_details cod ON cie.parent = cod.objectid  WHERE cie.parent = ".intval($object_id)." AND cie.objectid <>".intval($object_id)." ORDER BY co.startdate ASC";
      }
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $old_datas = $db->loadAssocList();
      $old_data = array();
      $old_interviewers = array();
      foreach ($old_datas as $k => $o) {
         $old_interviewers[] = $o['uid'];
         $old_data[$o['uid']] = JHTML::_('date',  $o['startdate'], 'Y-m-d H:i:s').'-'.JHTML::_('date',  $o['enddate'], 'Y-m-d H:i:s');
      }

      // Pre-operations for interviewers edit
      $interviewers =  array_filter(JRequest::getVar('interviewer', array(), 'post', 'array')); 
      $starttime = array_filter(JRequest::getVar('starttime',array(),'post','array'));
      $endtime = array_filter(JRequest::getVar('endtime', array(), 'post', 'array'));
      $sendsms = JRequest::getString('sendsms','');

      $interviewer_data = array();
      foreach ($interviewers as $k => $v) { 
         $start_time =  PFactory::getDate($post['from'].$starttime[$k].':00')->format('Y-m-d H:i:s');
         $end_time =  PFactory::getDate($post['from'].$endtime[$k].':00')->format('Y-m-d H:i:s');
         if (!empty($v)) {
            $interviewer_data[] = array(
               'uid' => $v,
               'accessclass' => $post['accessclass'],
               'objecttype' => $post['objecttype'],
               'jobseekername' => $post['jobseekername'],
               'jobtitle' => $post['jobtitle'],
               'start_time' => $start_time,
               'end_time' => $end_time,
               'action' => null
            );
            $post_data[$v] = $start_time.'-'.$end_time;
         }
      }

      //get new add interviewers
      $newaddinterviewers = $interviewers;

      //get delete interviewers
      $deleteinterviewers = $old_interviewers;

      //get no notify data
      $nonotifydata = array_intersect_key($post_data, $old_data);
      
      $changeddata = array_diff_assoc($post_data,$old_data);
      $changedinterviewers = array();
      foreach($changeddata as $k => $d){
         $changedinterviewers[] = $k;
      }

      $delete_object = array();
      
      foreach($old_datas as $o){
         if(in_array($o['uid'], $deleteinterviewers)){
            $delete_object[$o['objectid']] = $o['uid'];
         }

      }
      
      $numinterviewers = count($interviewer_data);

      // Get edit totalinterviewers by objectid
      $query = "SELECT numinterviewers FROM *PREFIX*calendar_objects_details WHERE objectid = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $edittotalinterviews =  $db->loadResult();

      // Update master oc_calendar_objects_details
      $query = "UPDATE *PREFIX*calendar_objects_details SET `summary` = ".$db->quote($post['summary']).", `is_phone` = ".$is_phone.", `notify_text` = ".$db->quote($post['notify_text']).", `numinterviewers` = ".intval($numinterviewers).",`attachment` =".intval($post['attachment']).",`contact` =".$db->quote($post['contact']).",`contact_phone` =".$db->quote($post['contact_phone']).",`address` =".$db->quote($post['address'])." WHERE objectid = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      }

      //set jobseeker\interview result back
      //现在改为编辑的时候如果有反馈,js端提示,并且继续修改的话,将移除原来的反馈记录
      if($interview_form_type == PCalendarHelper::INTERVIEW_FORM_RESET || $interview_form_type == PCalendarHelper::INTERVIEW_FORM_EDIT){
         $interviewresult = 0;
         if($round == 1){
            PResumesHelper::setResumeStatus($jobseekerid,PCalendarHelper::JOBSEEKER_STATUS_INTERVIEW);
         }else{
            PResumesHelper::setResumeStatus($jobseekerid,PCalendarHelper::JOBSEEKER_STATUS_RETEST);
            $interviewresult = PCalendarHelper::INTERVIEW_RESULT_RETEST;
         }
         
         // Update interview result in oc_calendar_objects_details table
         // 取消后重新安排面试,状态改为 --
         $query ="UPDATE *PREFIX*calendar_objects_details SET result=0 , totalfeedbacks = 0 WHERE objectid= ".intval($object_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            return false;
         }
      }
      // Update master oc_calendar_objects_result
      // Get now totalinterviewers by last_interview_objectid
      $query = "SELECT totalinterviewers FROM *PREFIX*calendar_interview_result WHERE last_interview_objectid = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $totalinterviews =  $db->loadResult();

      // Get reset totalinterviewers
      $newtotalinterviews = $totalinterviews - ($edittotalinterviews - $numinterviewers);
      // Update totalinterviewers by last_interview_objectid
      // If the jobseeker gived up before,HR set new interview again,need unset rejected,reject_time
      $query = "UPDATE *PREFIX*calendar_interview_result SET rejected=0,reject_time='0000-00-00 00:00:00',totalinterviewers = ".$newtotalinterviews.", canceled = 0  WHERE last_interview_objectid = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      }
      // Need to delete jobseeker's lastest give up reasons
      $this->removeGiveup($jobseekerid,PFactory::getMasterUser()->id);
      //record the interviewer only change starttime or endtime
      $send_update_email = array();
      $send_new_email = array();
      $change_email = array();
      // Creat calenders(if dont have one) and objects for interviewers
      $calendar_model = new PCalendarModelCalendar();
      $app_model = new PCalendarModelApp();
      //post data has 3 cases: 1:update 2:add 3:nochanged
      foreach ($interviewer_data as $k => $each_data) {
         // Find sub calendar id
         $sub_calender = $calendar_model->findByUid($each_data['uid']);
         if ($sub_calender) {
            $sub_calenderid = $sub_calender['id'];
         } else {
            // Creat sub calender
            $sub_calenderid = $calendar_model->addCalendar($each_data['uid'],"Interviewer",'VEVENT,VTODO,VJOURNAL',$timezone);
         }
         $interviewer_data[$k]['calender_id'] = $sub_calenderid;
         // Create sub Vcanlendar
         $sub_vcal = $this->createInterviewerVCalendar($each_data);
         // Add sub object
         $sub_objectid = $this->addObject($sub_calenderid,$sub_vcal->serialize(),'sub');
         $interviewer_data[$k]['object_id'] = $sub_objectid;
         // Collect object id for email notify use
         if(!key_exists($each_data['uid'], $nonotifydata)) {
            $send_new_email[] = $sub_objectid;
         }
         foreach ($changedinterviewers as $key => $value) {
            if($value == $each_data['uid']){
               $change_email[] = $sub_objectid;
            }
         }
         $all_interviewer_email[] = $sub_objectid;
         // Add action mark
         $interviewer_data[$k]['action'] = 'add';
      }
      $send_update_email = array_diff($change_email,$send_new_email);
      if(!empty($send_update_email)){
         $query = "SELECT objectid,uid FROM *PREFIX*calendar_interview_events WHERE objectid IN(".implode(',', $send_update_email).")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
      }
      // Insert and update interviewer's oc_calendars_interview_events
      $value_arr = array();
      foreach ($interviewer_data as $k => $v) {
         if ( $v['action'] ==  'add') {
            $displayname = PCalendarHelper::getDisplayName($v['uid']);
            $value_arr[] = "(".intval($object_id).", 0, ".intval($v['object_id']).", ".$db->quote($v['objecttype']).", ".intval($post['jobid']).", ".$db->quote($v['uid']).", ".$db->quote($displayname).", ".intval($post['jobseekerid']).", ".$db->quote($v['jobseekername']).")";
         }
      }
      // Do inserts
      if ( !empty($value_arr) ) {
         $values = implode(",", $value_arr);
         $query = "INSERT INTO *PREFIX*calendar_interview_events
            (`parent`, `next`, `objectid`, `eventtype`, `jobid`, `uid`, `displayname`, `jobseekerid`, `jobseekername`)
            VALUES ".$values;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $db->leaveOnMaster();
            $this->setError("网络异常，请重试！");
            return false;
         }
      }

      foreach ($interviewer_data as $k => $v) {
         $emailArr[] = $v['uid'];
      }
      $sharedlist = ResumeShare::getShareWithList($post['jobseekerid'], $emailArr);
      $sharedlist = empty($sharedlist)?array():$sharedlist;
      $notsharedlist = array_values(array_unique(array_diff($emailArr, $sharedlist)));
      // Add share events to oc_share only for new user's
      foreach ($interviewer_data as $k => $v) {
         // No need to update old interviewer here
         if ( $v['action'] ==  'add') {
            $event_share_token = OCP\Share::shareItem('event', $v['object_id'], OCP\Share::SHARE_TYPE_LINK, $v['uid'], \OCP\PERMISSION_READ);
            if(in_array($v['uid'],$notsharedlist)){
               $resume_share_token = OCP\Share::shareItem('resumes', $post['jobseekerid'], OCP\Share::SHARE_TYPE_LINK, $v['uid'], \OCP\PERMISSION_READ);
            }
         }
      }

      // Delete removed interviewer's record
      foreach ($delete_object as $delete_ob_id => $uid) {
         // Send email notify interviewer, his interview has been canceled
         if ($interview_form_type != PCalendarHelper::INTERVIEW_FORM_RESET) {
            if(!key_exists($uid, $nonotifydata)) {
               $user = PCalendarHelper::getInterviewerEmailDataFromObjectid($delete_ob_id);
               PCalendarHelper::sendInterviewCancelEmail($user);
               $intervieweremailsent = true;
            }
         }

         // Delete oc_calendar_objects
         $query = "DELETE FROM *PREFIX*calendar_objects WHERE id = ".intval($delete_ob_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $db->leaveOnMaster();
            $this->setError("网络异常，请重试！");
            return false;
         }
         // Delete oc_calendar_interview_events
         $query = "DELETE FROM *PREFIX*calendar_interview_events WHERE objectid = ".intval($delete_ob_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $db->leaveOnMaster();
            $this->setError("网络异常，请重试！");
            return false;
         }
         // Delete Share events
         OCP\Share::unshareAll('event', $delete_ob_id);
      }

      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects', '*PREFIX*calendar_interview_events', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result', '*PREFIX*share'));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects-'.$object_id, '*PREFIX*calendar_interview_events-'.$object_id, '*PREFIX*calendar_objects_details-'.$object_id, '*PREFIX*calendar_interview_result-'.$object_id));
      
      // Send email to notify interviewer his event has been changed
      if ($interview_form_type != PCalendarHelper::INTERVIEW_FORM_RESET) {
         if(!empty($summaryChanged)){
            foreach ($all_interviewer_email as $k => $ob_id) {
               $user = PCalendarHelper::getInterviewerEmailDataFromObjectid($ob_id);
               PCalendarHelper::sendInterviewerEventEmail($user, 'add'); 
            }
            $intervieweremailsent = true;
         }else{
            if(!empty($send_update_email) ) {
              foreach ($send_update_email as $k => $ob_id) {
                 foreach ($old_datas as $value) {
                    if($value['objectid'] == $ob_id){
                       $user = PCalendarHelper::getInterviewerEmailDataFromObjectid($ob_id);
                       PCalendarHelper::sendInterviewerEventEmail($user, 'edit', $value);                     
                    }
                 }
              }
              $intervieweremailsent = true;
            }
            // Send email to notify interviewer he has a new event
            if ( !empty($send_new_email) ) {
              foreach ($send_new_email as $k => $ob_id) {
                 $user = PCalendarHelper::getInterviewerEmailDataFromObjectid($ob_id);
                 PCalendarHelper::sendInterviewerEventEmail($user, 'add');               
              }
              $intervieweremailsent = true;
            }
         }
         

         // Notify jobseeker his/her event has been changed
         if ( $date_changed  || $jobseeker_data_changed || (intval($post['attachment']) != $old_datas[0]['attachment'])) {
            $this->notifyJobseeker($object_id, 'edit',$sendsms);
            $jobseekeremailsent = true;
         }
      } else {
         $send_email = array();
         $send_email = PCalendarHelper::getInterviewerEmailDataFromParentid($object_id);
         if(!empty($summaryChanged)){
            foreach ($all_interviewer_email as $k => $ob_id) {
                $user = PCalendarHelper::getInterviewerEmailDataFromObjectid($ob_id);
                PCalendarHelper::sendInterviewerEventEmail($user, 'add'); 
            }
            $intervieweremailsent = true;
         }else{
            if (!empty($send_email)) {
               foreach ($send_email as $k => $user) {
                  PCalendarHelper::sendInterviewerEventEmail($user, 'add');
               }
               $intervieweremailsent = true;
            }
            if ( $date_changed  || $jobseeker_data_changed || (intval($post['attachment']) != $old_datas[0]['attachment'])) {
               $this->notifyJobseeker($object_id, 'add',$sendsms);
               $jobseekeremailsent = true;
            }
         }
           
      }
      // leave on master now 
      $db->leaveOnMaster(); 

      return true;
   }
   

   public function removeGiveup($jobseekerid,$owner){
      $db = PFactory::getDbo();
      $query="DELETE FROM *PREFIX*hr_jobseeker_tag_term_details WHERE jobseeker_id=".intval($jobseekerid). " AND owner=".intval($owner) . " AND tagid IN (SELECT id FROM *PREFIX*hr_jobseeker_tag_term WHERE name IN ('".implode("','",self::$validRejectReasons)."') AND owner=".intval($owner).")"; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();   
      return true;
   }
   /**
    * @brief edits an object (VTODO)
    * @param integer $id id of object
    * @param string $data  object
    * @return boolean
    */
   public function editTodo($id, $data, $post) {
      $db = PFactory::getDbo();
      // we need to stay on master on connection in duration of adding 
      $db->stayOnMaster(); 

      $force_edit = $allowedit = $this->checkObjectEditPermission($id);
      if ( !$allowedit ) {
         $db->leaveOnMaster();
         $this->setError("您没有管理该面试日程的权限！");
         return false;
      }

      // Update VTODO's vcalendar
      $this->editObject($id, $data, $force_edit, 'VTODO');

      // Extra data update
      $config = PFactory::getConfig();
      $timezone = $config->get('offset', 'Asia/Shanghai');
      $object_id = $id; // This is VTODO's object id

      // Since the user can only edit entrydeadline, so there is only one table need to be updated
      $entry_deadline = $post['from']." ".$post['fromtime'];
      $jobseekerid = $post['jobseekerid'];
      $entryenddate = $post['entryenddate'];
      $user = PFactory::getMasterUser();

      if(!empty($entryenddate)){
         if($entryenddate<=$post['from']){
          $db->leaveOnMaster();
            $this->setError("试用期结束时间必须大于入职时间！");
            return false;
         }
      }
      if(!PCalendarHelper::checkSendOfferDate($entry_deadline,$jobseekerid)){
         $db->leaveOnMaster();
         $this->setError("入职时间必须大于最后一轮面试时间！");
         return false;
      }

      if(!empty($post['copywith'])){
         $postcopywith = array();
         $postcopywith = array_unique(explode(',', str_replace(' ', '', $post['copywith'])));
         for($i=0;$i<count($postcopywith);$i++){
            if(!JMailHelper::isEmailAddress($postcopywith[$i])) {
               $this->setError("不合法的邮箱:".$postcopywith[$i]);
               return false;
            }
         }

         $query = "SELECT copywith FROM *PREFIX*calendar_offer_carboncopy WHERE objectid = ".intval($object_id);
         $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
         $olddata= $db->loadAssocList();
         $oldcopywith = array();
         if(!empty($olddata)){
            foreach($olddata as $val){
               $oldcopywith[] = $val['copywith'];
            }
         }
         //get new add copywith
         $newaddcopywith = array_diff($postcopywith,$oldcopywith);

         //get delete copywith
         $deletecopywith = array_diff($oldcopywith, $postcopywith);
         
         //get nochanged copywith (removal new add)
         $nochangedcopywith = array_diff(array_intersect($oldcopywith,$postcopywith),$deletecopywith);
         
         if(!empty($nochangedcopywith)){
            foreach ($nochangedcopywith as $val){
               $query = "UPDATE *PREFIX*calendar_offer_carboncopy SET copytime = ".$db->quote(time())." WHERE copywith = ".$db->quote($val)." AND objectid = ".intval($object_id);
               $db->setQuery(PUtil::replaceOCPrefix($query));
               if (!$db->execute()) {
                  $db->leaveOnMaster();
                  $this->setError('网络异常，请重试！');
                  return false;
               }
            }
         }

         if(!empty($deletecopywith)){
            foreach ($deletecopywith as $val){
               $query = "DELETE FROM *PREFIX*calendar_offer_carboncopy  WHERE copywith = ".$db->quote($val)." AND objectid = ".intval($object_id);
               $db->setQuery(PUtil::replaceOCPrefix($query));
               if (!$db->execute()) {
                  $db->leaveOnMaster();
                  $this->setError('网络异常，请重试！');
                  return false;
               }
            }
         }

         if(!empty($newaddcopywith)){
            foreach ($newaddcopywith as $val){
               $query = "INSERT INTO *PREFIX*calendar_offer_carboncopy (`uid`,`objectid`,`copywith`,`copytime`) VALUES(".intval($user->id).",".intval($object_id).",".$db->quote($val).",UTC_TIMESTAMP)";
               $db->setQuery(PUtil::replaceOCPrefix($query));
               if (!$db->execute()) {
                  $db->leaveOnMaster();
                  $this->setError('网络异常，请重试！');
                  return false;
               }
            }
         }
      }

      // Check old entry_deadline
      $query = "SELECT entry_deadline,entry_enddate,offer_text,department,attachment,entrysalary,salary FROM *PREFIX*calendar_offer WHERE objectid = ".intval($object_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $old_data = $db->loadAssoc();
      $old_data['entry_deadline'] = PFactory::getDate($old_data['entry_deadline']);
      $entry_deadline = PFactory::getDate($entry_deadline, $config->get("offset"));

      if (  $old_data['entry_deadline']->toUnix() == $entry_deadline->toUnix()
           && $old_data['entry_enddate'] == $entryenddate
           && $old_data['offer_text'] == $post['textval'] 
           && $old_data['department'] == $post['department']
           && $old_data['attachment'] == $post['attachmentid']
           && $old_data['entrysalary'] == $post['entrysalary']
           && $old_data['salary'] == $post['salary'])
      {
         // Since there is no change, Send no email to jobseeker
         $db->leaveOnMaster();
         return true;
      } else {
         $object = OC_VObject::parse($data);
         list($objecttype,$startdate,$enddate,$summary,$repeating,$uid) = $this->extractData($object);
         $query = "UPDATE *PREFIX*calendar_offer
            SET `entry_deadline` = ".$db->quote($startdate)."
            , `entry_enddate` = ".$db->quote($entryenddate)."
            , `offer_text` = ".$db->quote($post['textval'])."
            , `department` = ".$db->quote($post['department'])."
            , `attachment` = ".intval($post['attachmentid'])."
            , `entrysalary` = ".intval($post['entrysalary'])."
            , `salary` = ".intval($post['salary'])."
            , `modified` = UTC_TIMESTAMP 
            WHERE objectid = ".intval($object_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $db->leaveOnMaster();
            $this->setError("网络异常，请重试！");
            return false;
         }

         if($post['type']==1){
            $query = "SELECT last_interview_objectid FROM *PREFIX*calendar_interview_result WHERE jobseekerid = ".intval($jobseekerid);
            $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
            $lastinterviewobjectid = $db->loadResult();
            if(empty($lastinterviewobjectid)){
               $this->setError("网络异常，请重试！");
               return false;
            }      
            $query = "UPDATE *PREFIX*calendar_objects_details SET entry_stat= 0, offer_stat = 0 WHERE  objectid IN(".intval($object_id).",".intval($lastinterviewobjectid).")";
            $db->setQuery(PUtil::replaceOCPrefix($query));
            $db->execute();
            
            PResumesHelper::setResumeStatus($jobseekerid,PCalendarHelper::JOBSEEKER_STATUS_SENDED);
            // Get Email data
            $query = "SELECT e.source as jobsource,g.email,g.source, g.company_id, f.comp_name, f.contact_name, h.phone, c.jobseekerid, c.jobseekername, e.job_title, d.entry_deadline, d.salary, d.offer_text, GROUP_CONCAT(cc.copywith) AS copywith, ca.file_name, d.entry_enddate, d.department, d.entrysalary, d.attachment,IF(jus.email IS NOT NULL, jus.email, c.uid) AS company_email, se.token
               FROM *PREFIX*calendar_objects a
               LEFT JOIN *PREFIX*calendar_objects_details b ON b.objectid = a.id
               LEFT JOIN *PREFIX*calendar_interview_events c ON c.objectid = a.id              
               LEFT JOIN *PREFIX*calendar_offer d ON d.objectid = a.id
               LEFT JOIN *PREFIX*calendar_offer_attachment ca on ca.id = d.attachment
               LEFT JOIN *PREFIX*calendar_offer_carboncopy cc on cc.objectid = d.objectid
               LEFT JOIN #__jobs_job e ON e.id = b.jobid
               LEFT JOIN #__jobs_employer f ON f.user_id = e.employer_id
               LEFT JOIN *PREFIX*hr_jobseeker g ON g.id = c.jobseekerid
               LEFT JOIN #__comprofiler h ON h.user_id = e.employer_id
               LEFT JOIN #__users jus ON jus.id = c.uid
               LEFT JOIN *PREFIX*share se ON se.item_source = c.objectid AND se.share_with = g.email AND se.item_type = 'offer'
               WHERE a.id = ".intval($object_id);
            $db->setQuery(PUtil::replaceOCPrefix($query));
            $user = $db->loadAssoc();
            // Send jobseeker notify again
            PCalendarHelper::sendOfferEmail($user);

            if(!empty($postcopywith)){
               foreach($postcopywith as $key=>$val){
                  $user['copywithemail'] = $val;
                  PCalendarHelper::sendOfferEmail($user,'copywith');
               }
            }
         }
      }

      // leave on master now 
      $db->leaveOnMaster();

      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects-'.$object_id, '*PREFIX*calendar_interview_events-'.$object_id, '*PREFIX*calendar_objects_details-'.$object_id, '*PREFIX*calendar_interview_result-'.$object_id, '*PREFIX*calendar_offer-'.$object_id,'*PREFIX*hr_jobseeker-'.$jobseekerid));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects', '*PREFIX*calendar_interview_events', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result', '*PREFIX*calendar_offer','*PREFIX*hr_jobseeker'));

      return true;
   }

   /**
    * @brief edits an object (sub function)
    * @param integer $id id of object
    * @param string $data  object
    * @param string $force_edit  set a permission allow no-owner to edit this event
    * @param string $ob_type  VEVENT or VTODO
    * @return boolean
    */
   public function editObject($id, $data, $force_edit=false, $ob_type=null) {
      $calendar_model = new PCalendarModelCalendar();
      $app_model = new PCalendarModelApp();
      $oldobject = $this->find($id);

      if ( !$force_edit ) {
         $calendar = $calendar_model->find($oldobject['calendarid']);
         $oldvobject = OC_VObject::parse($oldobject['calendardata']);
         if ($calendar['userid'] != OC_USER::getUser()) {
            $sharedCalendar = OCP\Share::getItemSharedWithBySource('calendar', $id);
            $sharedAccessClassPermissions = $app_model->getAccessClassPermissions($oldvobject->VEVENT->CLASS->value);
            if (!$sharedCalendar || !($sharedCalendar['permissions'] & OCP\PERMISSION_UPDATE) || !($sharedAccessClassPermissions & OCP\PERMISSION_UPDATE)) {
               $this->setError("你没有权限修改该日程！");
               return false;
            }
         }
      }

      $object = OC_VObject::parse($data);
      $app_model->loadCategoriesFromVCalendar($id, $object);
      list($objecttype,$startdate,$enddate,$summary,$repeating,$uid) = $this->extractData($object);

      if ( $ob_type == 'VTODO')
         $objecttype = $ob_type;

      $db = PFactory::getDbo();
      $query = "UPDATE *PREFIX*calendar_objects SET objecttype = ".$db->quote($objecttype).", startdate = ".$db->quote($startdate).", enddate = ".$db->quote($enddate).", repeating = ".intval($repeating).", summary = ".$db->quote($summary).", calendardata = '".$data."', lastmodified = ".time()." WHERE id = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError("网络异常，请重试！");
         return false;
      }

      $calendar_model->touchCalendar($oldobject['calendarid']);
      OCP\Util::emitHook('OC_Calendar', 'editEvent', $id);

      return true;
   }

   /**
    * @brief edits an object with the data provided by sabredav
    * @param integer $id calendar id
    * @param string $uri   the uri of the object
    * @param string $data  object
    * @return boolean
    */
   public function editFromDAVData($cid,$uri,$data) {
      $app_model = new PCalendarModelApp();
      $calendar_model = new PCalendarModelCalendar();
      $oldobject = $this->findWhereDAVDataIs($cid,$uri);

      $calendar = $calendar_model->find($cid);
      $oldvobject = OC_VObject::parse($oldobject['calendardata']);
      if ($calendar['userid'] != OC_USER::getUser()) {
         $sharedCalendar = OCP\Share::getItemSharedWithBySource('calendar', $cid);
         $sharedAccessClassPermissions = $app_model->getAccessClassPermissions($oldvobject->VEVENT->CLASS->value);
         if (!$sharedCalendar || !($sharedCalendar['permissions'] & OCP\PERMISSION_UPDATE) || !($sharedAccessClassPermissions & OCP\PERMISSION_UPDATE)) {
            $this->setError("你没有权限编辑该日程!");
            return false;
         }
      }
      $object = OC_VObject::parse($data);
      list($type,$startdate,$enddate,$summary,$repeating,$uid) = $this->extractData($object);

      $db = PFactory::getDbo();
      $query = "UPDATE *PREFIX*calendar_objects SET objecttype = ".$db->quote($type).", startdate = ".$db->quote($startdate).", enddate = ".$db->quote($enddate).", repeating = ".intval($repeating).", summary = ".$db->quote($summary).", calendardata = '".$data."', lastmodified = ".time()." WHERE id = ".intval($oldobject['id']);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError("网络异常，请重试！");
         return false;
      }

      $calendar_model->touchCalendar($oldobject['calendarid']);
      OCP\Util::emitHook('OC_Calendar', 'editEvent', $oldobject['id']);

      return true;
   }

   /**
    * @brief deletes an object
    * @param integer $id id of object
    * @return boolean
    */
   public function delete($id) {
      $db = PFactory::getDbo();
      // we need to stay on master on connection in duration of adding 
      $db->stayOnMaster();
      
      $calendar_model = new PCalendarModelCalendar();
      $app_model = new PCalendarModelApp();

      $oldobject = $this->find($id);
      $calendar = $calendar_model->find($oldobject['calendarid']);
      $object = OC_VObject::parse($oldobject['calendardata']);
      if ($calendar['userid'] != OC_USER::getUser()) {
         $sharedCalendar = OCP\Share::getItemSharedWithBySource('calendar', $id);
         $sharedAccessClassPermissions = $app_model->getAccessClassPermissions($object->VEVENT->CLASS->value);
         if (!$sharedCalendar || !($sharedCalendar['permissions'] & OCP\PERMISSION_DELETE) || !($sharedAccessClassPermissions & OCP\PERMISSION_DELETE)) {
            $db->leaveOnMaster();
            $this->setError("你没有权限删除此日程！");
            return false;
         }
      }

      $query = "DELETE FROM *PREFIX*calendar_objects WHERE id = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setError("网络异常，请重试！");
         return false;
      }

      $calendar_model->touchCalendar($oldobject['calendarid']);

      OCP\Share::unshareAll('event', $id);

      OCP\Util::emitHook('OC_Calendar', 'deleteEvent', $id);

      $app_model->getVCategories()->purgeObject($id);
   
      // leave on master now 
      $db->leaveOnMaster();

      return true;
   }

   /**
    * @brief deletes an  object with the data provided by sabredav
    * @param integer $cid calendar id
    * @param string $uri the uri of the object
    * @return boolean
    */
   public function deleteFromDAVData($cid,$uri) {
      $calendar_model = new PCalendarModelCalendar();
      $oldobject = $this->findWhereDAVDataIs($cid, $uri);
      $calendar = $calendar_model->find($cid);
      if ($calendar['userid'] != OC_USER::getUser()) {
         $sharedCalendar = OCP\Share::getItemSharedWithBySource('calendar', $cid);
         if (!$sharedCalendar || !($sharedCalendar['permissions'] & OCP\PERMISSION_DELETE)) {
            $this->setError("你没有权限删除该日程!");
            return false;
         }
      }

      $db = PFactory::getDbo();
      $query = "DELETE FROM *PREFIX*calendar_objects WHERE calendarid = ".intval($cid)." AND uri = ".$db->quote($uri);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError("网络异常，请重试！");
         return false;
      }

      $calendar_model->touchCalendar($cid);
      OCP\Util::emitHook('OC_Calendar', 'deleteEvent', $oldobject['id']);

      return true;
   }

   /* Remove function moveToCalendar */

   /**
     * @brief Creates a UID
     * @return string
     */
    protected function createUID() {
        return substr(md5(rand().time()),0,10);
    }

   /**
    * @brief Extracts data from a vObject-Object
    * @param Sabre_VObject $object
    * @return array
    *
    * [type, start, end, summary, repeating, uid]
    */
   protected function extractData($object) {
      $return = array('',null,null,'',0,null);

      // Child to use
      $children = 0;
      $use = null;
      foreach($object->children as $property) {
         if($property->name == 'VEVENT') {
            $children++;
            $thisone = true;

            foreach($property->children as &$element) {
               if($element->name == 'RECURRENCE-ID') {
                  $thisone = false;
               }
            } unset($element);

            if($thisone) {
               $use = $property;
            }
         }
         elseif($property->name == 'VTODO' || $property->name == 'VJOURNAL') {
            $return[0] = $property->name;
            foreach($property->children as &$element) {
               if($element->name == 'SUMMARY') {
                  $return[3] = $element->value;
               }
               elseif($element->name == 'UID') {
                  $return[5] = $element->value;
               }
            };

            // Only one VTODO or VJOURNAL per object
            // (only one UID per object but a UID is required by a VTODO =>
            //    one VTODO per object)
            break;
         }
      }

      // find the data
      if(!is_null($use)) {
         $return[0] = $use->name;
         foreach($use->children as $property) {
            if($property->name == 'DTSTART') {
               $return[1] = $this->getUTCforMDB($property->getDateTime());
            }
            elseif($property->name == 'DTEND') {
               $return[2] = $this->getUTCforMDB($property->getDateTime());
            }
            elseif($property->name == 'SUMMARY') {
               $return[3] = $property->value;
            }
            elseif($property->name == 'RRULE') {
               $return[4] = 1;
            }
            elseif($property->name == 'UID') {
               $return[5] = $property->value;
            }
         }
      }

      // More than one child means reoccuring!
      if($children > 1) {
         $return[4] = 1;
      }
      return $return;
   }

   /**
    * @brief DateTime to UTC string
    * @param DateTime $datetime The date to convert
    * @returns date as YYYY-MM-DD hh:mm
    *
    * This function creates a date string that can be used by MDB2.
    * Furthermore it converts the time to UTC.
    */
   public function getUTCforMDB($datetime) {
      return date('Y-m-d H:i', $datetime->format('U'));
   }

   /**
    * @brief returns the DTEND of an $vevent object
    * @param object $vevent vevent object
    * @return object
    */
   public function getDTEndFromVEvent($vevent) {
      if ($vevent->DTEND) {
         $dtend = $vevent->DTEND;
      }else{
         $dtend = clone $vevent->DTSTART;
         // clone creates a shallow copy, also clone DateTime
         $dtend->setDateTime(clone $dtend->getDateTime(), $dtend->getDateType());
         if ($vevent->DURATION) {
            $duration = strval($vevent->DURATION);
            $invert = 0;
            if ($duration[0] == '-') {
               $duration = substr($duration, 1);
               $invert = 1;
            }
            if ($duration[0] == '+') {
               $duration = substr($duration, 1);
            }
            $interval = new DateInterval($duration);
            $interval->invert = $invert;
            $dtend->getDateTime()->add($interval);
         }
      }
      return $dtend;
   }

   /**
    * @brief Remove all properties which should not be exported for the AccessClass Confidential
    * @param string $calendarId Calendar ID
    * @param Sabre_VObject $vobject Sabre VObject
    * @return object
    */
   public function cleanByAccessClass($calendarId, $vobject) {
      // Do not clean your own calendar
      if($this->getOwner($calendarId) === OC_USER::getUser()) {
         return $vobject;
      }

      $vevent = $vobject->VEVENT;
      if(!is_null($vevent->CLASS) && $vevent->CLASS->value == 'CONFIDENTIAL') {
         foreach ($vevent->children as &$property) {
            switch($property->name) {
               case 'CREATED':
               case 'DTSTART':
               case 'RRULE':
               case 'DURATION':
               case 'DTEND':
               case 'CLASS':
               case 'UID':
                  break;
               case 'SUMMARY':
                  $property->value = 'Busy';
                  break;
               default:
                  $vevent->__unset($property->name);
                  break;
            }
         }
      }
      return $vobject;
   }

   /* Remove function getAccessClassOptions */
   /* Remove function getRepeatOptions */
   /* Remove function getEndOptions */
   /* Remove function getMonthOptions */
   /* Remove function getWeeklyOptions */
   /* Remove function getYearOptions */
   /* Remove function getWeekofMonth */
   /* Remove function getByYearDayOptions */
   /* Remove function getByMonthDayOptions */
   /* Remove function getByMonthOptions */
   /* Remove function getByWeekNoOptions */

   /**
    * @brief validates a request
    * @param array $request
    * @return mixed (array / boolean)
    */
   public function validateRequest($request) {
      // Basic check
      if($request['jobid'] == ''
         || $request['jobtitle'] == ''
         || $request['jobseekerid'] == ''
         || $request['jobseekername'] == ''
         || $request['accessclass'] != 'PUBLIC'
         || $request['calendar'] == '')
      {
         $this->setError("网络拥挤，请刷新页面重试！");
         return false;
      }

      $interviewers = array_filter($request['interviewer']);
      $starttime = array_filter($request['starttime']);
      $endtime = array_filter($request['endtime']);
      
      //if exit starttime but dut exist interviewer and endtime,let's go
      foreach($starttime as $k=>$s){
         if(empty($interviewers[$k]) && empty($endtime[$k])){
            unset($starttime[$k]);
            unset($request['starttime'][$k]);
            unset($request['endtime'][$k]);
            unset($request['interviewer'][$k]);
         }
      }

      // At least enter one interviewer
      if( empty($interviewers) ) {
         $this->setError("请通知至少一位面试官！");
         return false;
      }

      // At least enter one during time
      if( empty($starttime) || empty($endtime)) {
         $this->setError("请输入面试时间！");
         return false;
      }

      // Validate email format
      jimport('joomla.mail.helper');
      $jobSeekerEmail = PResumesHelper::getJobSeekerEmail($request['jobseekerid']);
      foreach ($interviewers as $k => $each) {
         if($jobSeekerEmail == $each) {
            $this->setError("面试官邮箱不能与应聘者邮箱相同！");
            return false;
         }
         if( !JMailHelper::isEmailAddress($each) ) {
            $this->setError("请输入正确的email格式！");
            return false;
         }
      }

      // Validate email array is unique array_unique
      $uni_interviewers = array_unique($interviewers);
      if ( count($interviewers) != count($uni_interviewers) ) {
         $this->setError("请勿重复输入面试官！");
         return false;
      }

      $interviewer_keys = array_keys($interviewers);
      $starttime_keys = array_keys($starttime);
      $endtime_keys = array_keys($endtime);
      
      $starttime_diff = array_diff($interviewer_keys, $starttime_keys);
      $endtime_diff = array_diff($interviewer_keys, $endtime_keys);
      
      $interview_diff_s = array_diff($starttime_keys, $interviewer_keys);
      $interview_diff_e = array_diff($endtime_keys, $interviewer_keys);
      // Validate each interviewer has during
      if ( count($starttime_diff) > 0 || count($endtime_diff) > 0) {
         $this->setError("缺少对应的面试时间！");
         return false;
      }

      // Validate each start and end  has interviewer
      if ( count($interview_diff_s) > 0  || count($interview_diff_e) > 0) {
         $this->setError("缺少对应的面试官！");
         return false;
      }
      //validate each start and end time
      $config = PFactory::getConfig();

      for ($i=0; $i < count($starttime); $i++) {
        if(!$this->checkTime(urldecode($starttime[$i])) || !$this->checkTime(urldecode($endtime[$i]))){
            $this->setError("面试官的时间格式有误！");
            return false;
         }else{
           $sstart = PFactory::getDate($request['from'].' '.$request['fromtime'], $config->get("offset"))->toUnix();
           $start = PFactory::getDate($request['from'].' '.$starttime[$i], $config->get("offset"))->toUnix();
           $end = PFactory::getDate($request['from'].' '.$endtime[$i], $config->get("offset"))->toUnix();
           if($start >= $end){
              $this->setError("面试官的结束时间要大于对应的开始时间！");
              return false;
           }else if($sstart > $start){
              $this->setError("面试官的开始时间不能小于总开始时间！");
              return false;
           }
         }
      }

      // Validate start date
      if(!$this->checkDate($request['from'])) {
         $this->setError("开始时间格式有误！");
         return false;
      }

      if( !$this->checkTime(urldecode($request['fromtime'])) ) {
         $this->setError("开始时间格式有误！");
         return false;
      }

      $objectid = 0;
      if(!empty($request['id'])){
         $objectid = $request['id'];
      }elseif(!empty($request['current_objectid'])){
         $objectid = $request['current_objectid'];
      }

      $config = PFactory::getConfig();
      $starttime = $request['from'].' '.$request['fromtime'];
      $starttime = PFactory::getDate($starttime, $config->get("offset"));

      $now = PFactory::getDate();

      if($starttime->toUnix() <= $now->toUnix()){
         $this->setError("开始时间必须大于现在时间！");
         return false;
      }elseif(!empty($objectid) && !empty($request['interview_form_type'])){
         $round = 0;
         if(!empty($request['round']))
            $round = $request['round'];

         //if round = 1 and interview form = edit or reset we dont need check the pre round time
         if(!($round == 1 && ($request['interview_form_type'] == PCalendarHelper::INTERVIEW_FORM_RESET || $request['interview_form_type'] == PCalendarHelper::INTERVIEW_FORM_EDIT))){
            if(($round >1 && ($request['interview_form_type'] == PCalendarHelper::INTERVIEW_FORM_RESET) || $request['interview_form_type'] == PCalendarHelper::INTERVIEW_FORM_EDIT)){
               $round = 0;
            }

            $lastroundtime = PCalendarHelper::getLastRoundTimeByObjectid($objectid,$round);
            $lastroundtime = PFactory::getDate($lastroundtime);
            if($starttime->toUnix() <= $lastroundtime->toUnix()){
               $this->setError("开始时间必须大于上一轮时间！");
               return false;
            }
         }
      }
      return $request;
   }

   /**
    * @brief validates a todo edit request
    * @param array $request
    * @return mixed (array / boolean)
    */
   public function validateTodoRequest($request) {
      // Basic check
      if( $request['id'] == ''
         || $request['jobid'] == ''
         || $request['jobtitle'] == ''
         || $request['jobseekerid'] == ''
         || $request['jobseekername'] == ''
         || $request['accessclass'] != 'PUBLIC'
         || $request['objecttype'] != 'VTODO'
         || $request['calendar'] == '')
      {
         $this->setError("网络拥挤，请刷新页面重试！");
         return false;
      }

      // Validate date
      if(!$this->checkDate($request['from'])) {
         $this->setError("入职日期格式有误！");
         return false;
      }

      if(!empty($request['entryendate'])){
         if(!$this->checkDate($request['entryendate'])) {
           $this->setError("试用期结束日期格式有误！");
           return false;
         }
      }

      if( !$this->checkTime(urldecode($request['fromtime'])) ) {
         $this->setError("入职时间格式有误！");
         return false;
      }

      // Must enter salary
      if( !empty($request['entrysalary']) && intval($request['entrysalary']) <= 0) {
         $this->setError("试用期月薪必须是正整数！");
         return false;
      }

      if( !empty($request['salary']) && intval($request['salary']) <= 0 ) {
         $this->setError("转正月薪必须是正整数！");
         return false;
      }

      return true;
   }

   /**
    * @brief validates time
    * @param string $time
    * @return boolean
    */
   protected function checkTime($time) {
      if(strpos($time, ':') === false ) {
         return false;
      }
      list($hour, $min) = explode(':', $time);
      if ($hour < 0 || $hour > 23 || !is_numeric($hour)) {
         return false;
      }
      if ($min < 0 || $min > 59 || !is_numeric($min)) {
         return false;
      }
      return true;
   }

   /**
    * @brief creates an VCalendar Object from the request data
    * @param array $request
    * @return object created $vcalendar
    *
    */
   public function createVCalendarFromRequest($request) {
      $vcalendar = new OC_VObject('VCALENDAR');
      $vcalendar->add('PRODID', 'Pipahr Calendar');
      $vcalendar->add('VERSION', '2.0');

      $vevent = new OC_VObject('VEVENT');
      $vcalendar->add($vevent);

      $vevent->setDateTime('CREATED', 'now', Sabre\VObject\Property\DateTime::UTC);

      $vevent->setUID();
      return $this->updateVCalendarFromRequest($request, $vcalendar);
   }

   /**
    * @brief updates an VCalendar Object from the request data
    * @param array $request
    * @param object $vcalendar
    * @return object updated $vcalendar
    */
   public function updateVCalendarFromRequest($request, $vcalendar) {
      $accessclass = $request["accessclass"];
      $ob_type = $request["objecttype"];
      switch ($ob_type) {
         case 'VEVENT':
            $title = "面试：".$request["jobseekername"]."(".$request['jobtitle'].")";
            break;
         case 'VTODO':
            $title = "入职：".$request["jobseekername"]."(".$request['jobtitle'].")";
            break;
         default:
            $title = "面试：".$request["jobseekername"]."(".$request['jobtitle'].")";
            break;
      }

      $categories = '';
      $from = $request["from"];
      $fromtime = $request['fromtime'];

      $vevent = $vcalendar->VEVENT;

      $repeat = "false";

      $vevent->setDateTime('LAST-MODIFIED', 'now', Sabre\VObject\Property\DateTime::UTC);
      $vevent->setDateTime('DTSTAMP', 'now', Sabre\VObject\Property\DateTime::UTC);
      $vevent->setString('SUMMARY', $title);

      $config = PFactory::getConfig();
      $timezone = $config->get('offset', 'Asia/Shanghai');
      $timezone = new DateTimeZone($timezone);

      // Format VEVENT & VTODO startdate and enddate
      $start_time = $from.' '.$fromtime;
      if ( $ob_type == 'VEVENT' ) {
         $endtime = array_filter($request['endtime']);
         $endtime_keys = array_keys($endtime);

         $end_time = PFactory::getDate($from.' '.$endtime[$endtime_keys[count($endtime)-1]])->format('Y-m-d H:i:s');
      } else { // Otherwise format time as VTODO
         $thisday = PFactory::getDate($start_time)->toUnix(); 
         $end_time = PFactory::getDate($thisday+24*60*60)->format('Y-m-d');
      }

      $start = new DateTime($start_time, $timezone);
      $start->setTimezone(new DateTimeZone('UTC'));
      $end = new DateTime($end_time, $timezone);
      $end->setTimezone(new DateTimeZone('UTC'));

      $vevent->setDateTime('DTSTART', $start, Sabre\VObject\Property\DateTime::UTC);
      $vevent->setDateTime('DTEND', $end, Sabre\VObject\Property\DateTime::UTC);

      unset($vevent->DURATION);

      $vevent->setString('CLASS', $accessclass);
      $vevent->setString('CATEGORIES', $categories);

      return $vcalendar;
   }

   /**
    * @brief creates an VCalendar Object for interviewer
    * @param array $each_data
    * @return object created $vcalendar
    *
    */
   public function createInterviewerVCalendar($each_data) {
      $vcalendar = new OC_VObject('VCALENDAR');
      $vcalendar->add('PRODID', 'Pipahr Calendar');
      $vcalendar->add('VERSION', '2.0');

      $vevent = new OC_VObject('VEVENT');
      $vcalendar->add($vevent);

      $vevent->setDateTime('CREATED', 'now', Sabre\VObject\Property\DateTime::UTC);

      $vevent->setUID();
      return $this->updateInterviewerVCalendar($each_data, $vcalendar);
   }

   /**
    * @brief updates an VCalendar Object for interviewer
    * @param array $each_data
    * @param object $vcalendar
    * @return object updated $vcalendar
    */
   public function updateInterviewerVCalendar($each_data, $vcalendar) {
      $accessclass = $each_data["accessclass"];
      $ob_type = $each_data["objecttype"];
      switch ($ob_type) {
         case 'VEVENT':
            $title = "面试：".$each_data["jobseekername"]."(".$each_data['jobtitle'].")";
            break;
         case 'VTODO':
            $title = "入职：".$each_data["jobseekername"]."(".$each_data['jobtitle'].")";
            break;
         default:
            $title = "面试：".$each_data["jobseekername"]."(".$each_data['jobtitle'].")";
            break;
      }
      $categories = '';
      $allday = false;
      $start_time = $each_data["start_time"];
      $end_time = $each_data['end_time'];

      $vevent = $vcalendar->VEVENT;

      $repeat = "false";

      $vevent->setDateTime('LAST-MODIFIED', 'now', Sabre\VObject\Property\DateTime::UTC);
      $vevent->setDateTime('DTSTAMP', 'now', Sabre\VObject\Property\DateTime::UTC);
      $vevent->setString('SUMMARY', $title);
      $vevent->setString('PRODID', 'Pipahr Calendar');

      // Remove allday
      $config = PFactory::getConfig();
      $timezone = $config->get('offset', 'Asia/Shanghai');
      $timezone = new DateTimeZone($timezone);

      $start = new DateTime($start_time, $timezone);
      $start->setTimezone(new DateTimeZone('UTC'));
      $end = new DateTime($end_time, $timezone);
      $end->setTimezone(new DateTimeZone('UTC'));

      $vevent->setDateTime('DTSTART', $start, Sabre\VObject\Property\DateTime::UTC);
      $vevent->setDateTime('DTEND', $end, Sabre\VObject\Property\DateTime::UTC);

      unset($vevent->DURATION);

      $vevent->setString('CLASS', $accessclass);
      $vevent->setString('CATEGORIES', $categories);

      return $vcalendar;
   }

   /**
    * @brief returns the owner of an object
    * @param integer $id
    * @return string
    */
   public function getOwner($id) {
      $calendar_model = new PCalendarModelCalendar();
      $event = $this->find($id);
      $cal = $calendar_model->find($event['calendarid']);
      if(array_key_exists('userid', $cal)){
         return $cal['userid'];
      }else{
         return null;
      }
   }

   /**
    * @brief returns the calendarid of an object
    * @param integer $id
    * @return integer
    */
   public function getCalendarid($id) {
      $event = $this->find($id);
      return $event['calendarid'];
   }

   /* Remove function isrepeating */

   /**
    * @brief converts the start_dt and end_dt to a new timezone
    * @param object $dtstart
    * @param object $dtend
    * @param boolean $allday
    * @param string $tz
    * @return array
    */
   public function generateStartEndDate($dtstart, $dtend, $allday, $tz) {
      $start_dt = $dtstart->getDateTime();
      $end_dt = $dtend->getDateTime();
      $return = array();
      // Remove all day
      $start_dt->setTimezone(new DateTimeZone($tz));
      $end_dt->setTimezone(new DateTimeZone($tz));
      $return['start'] = $start_dt->format('Y-m-d H:i:s');
      $return['end'] = $end_dt->format('Y-m-d H:i:s');
      return $return;
   }

   /**
    * @brief Get Event's Extra Data
    * @param  integer $object_id master object id
    * @return object
    */
   public function getEventExtraData($object_id) {
      $db = PFactory::getDbo();
      $master_uid = PFactory::getMasterUser()->get("id");
      $query = "SELECT jj.id AS jobid, jj.job_title, ie.jobseekerid,ie.jobseekername, od.notify_text, od.summary, od.is_phone, of.id AS attachment, of.file_name, od.round, cir.totalfeedbacks, od.address, od.contact_phone, od.contact, od.totalfeedbacks AS last_round_totalfeedbacks 
         FROM *PREFIX*calendar_objects_details od
         LEFT JOIN *PREFIX*calendar_interview_events ie ON od.objectid = ie.objectid
         LEFT JOIN #__jobs_job jj ON od.jobid = jj.id
         LEFT JOIN *PREFIX*calendar_offer_attachment of ON od.attachment = of.id 
         LEFT JOIN *PREFIX*calendar_interview_result cir ON cir.last_interview_objectid = od.objectid 
         WHERE jj.employer_id = ".intval($master_uid)." AND od.objectid = ".intval($object_id);
      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getEventExtraData'), $query);
      // Event Driven Cache/Object Caching
      CacheHelper::setTriggers(PFactory::getMasterUser()->get("id"), $cache, array('*PREFIX*calendar_objects_details-'.$object_id, '*PREFIX*calendar_interview_events-'.$object_id, '#__jobs_job'));

      return $result;
   }

   public function _getEventExtraData($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   /**
    * @brief Get VTodo's Extra Data
    * @param  integer $object_id master object id
    * @return object
    */
   public function getTodoExtraData($object_id) {
      $db = PFactory::getDbo();
      $master_uid = PFactory::getMasterUser()->get("id");
      $query = "SELECT jj.id AS jobid, jj.job_title, ie.jobseekerid, ie.jobseekername, co.salary, co.department, co.offer_text, co.entry_deadline, co.entry_enddate, co.entrysalary, coa.file_name, co.attachment, GROUP_CONCAT(cc.copywith) AS copywith
         FROM *PREFIX*calendar_objects_details od
         LEFT JOIN *PREFIX*calendar_interview_events ie ON od.objectid = ie.objectid
         LEFT JOIN #__jobs_job jj ON od.jobid = jj.id
         LEFT JOIN *PREFIX*calendar_offer co ON co.objectid = od.objectid
         LEFT JOIN *PREFIX*calendar_offer_attachment coa ON coa.id = co.attachment
         LEFT JOIN *PREFIX*calendar_offer_carboncopy cc ON cc.objectid = co.objectid
         WHERE jj.employer_id = ".intval($master_uid)." AND od.objectid = ".intval($object_id);

      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getTodoExtraData'), $query);
      // Event Driven Cache/Object Caching
      CacheHelper::setTriggers(PFactory::getMasterUser()->get("id"), $cache, array('*PREFIX*calendar_objects_details-'.$object_id, '*PREFIX*calendar_interview_events-'.$object_id, '*PREFIX*calendar_offer-'.$object_id, '#__jobs_job'));

      return $result;
   }

   public function _getTodoExtraData($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   /**
    * @brief Get Event's Interviewers
    * @param  integer $object_id master object id
    * @return object
    */
   public function getEventInterviewers($object_id,$master_uid=null) {
      $db = PFactory::getDbo();
      $master_uid = empty($master_uid) ? PFactory::getMasterUser()->get('id') : intval($master_uid);
      $query = "SELECT
                IF(cie.displayname IS NOT NULL AND length(cie.displayname)>0, cie.displayname, cie.uid) AS show_name, cie.uid AS email, co.startdate, co.enddate, s.token, fs.suggestion, cie.objectid, fa.suggestion_id
                FROM *PREFIX*calendar_interview_events cie
                LEFT JOIN *PREFIX*calendar_objects co ON co.id = cie.objectid
                LEFT JOIN *PREFIX*share s ON s.share_with = cie.uid AND s.item_source = cie.objectid
                LEFT JOIN #__jobs_job jj ON jj.id = cie.jobid
                LEFT JOIN *PREFIX*calendar_feedback_answer fa ON fa.objectid = cie.objectid
                LEFT JOIN *PREFIX*calendar_feedback_suggestion fs ON fs.id = fa.suggestion_id
                WHERE jj.employer_id = ".intval($master_uid)." AND cie.parent = ".intval($object_id)." AND cie.objectid <> ".intval($object_id)."
                GROUP BY cie.objectid ORDER BY startdate ASC";
      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getEventInterviewers'), $query);
      // Event Driven Cache/Object Caching
      CacheHelper::setTriggers(PFactory::getMasterUser()->get("id"), $cache, array('*PREFIX*calendar_objects-'.$object_id, '*PREFIX*calendar_interview_events-'.$object_id, '*PREFIX*calendar_feedback_answer-'.$object_id,'*PREFIX*share', '#__jobs_job'));

      return $result;
   }

   public function _getEventInterviewers($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }

   /**
    * @brief Get Event's Preview Info
    * @param  integer $object_id master object id
    * @return object
    */
   public function getEventPreviewInfo($object_id,$master_uid=null) {
      $db = PFactory::getDbo();
      // Check if this objectid is master object
      $query = "SELECT parent FROM *PREFIX*calendar_interview_events WHERE objectid = ".intval($object_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $parent_id = $db->loadResult();
      if ( !empty($parent_id) ) {
         $object_id = $parent_id; // Replace objectid to parentid if it's not a master objectid
      }

      $master_uid = empty($master_uid) ? PFactory::getMasterUser()->get('id') : intval($master_uid);
      // Do master object query
      $query = "SELECT jj.id AS jobid, jj.job_title AS jobtitle, ie.jobseekerid, ie.jobseekername, o.startdate, o.objecttype, o.id AS objectid, od.round, od.result, od.entry_stat, od.offer_stat, ir.last_interview_objectid,ir.last_offer_objectid,ohj.email
         FROM *PREFIX*calendar_objects o
         LEFT JOIN *PREFIX*calendar_objects_details od ON od.objectid = o.id
         LEFT JOIN *PREFIX*calendar_interview_events ie ON ie.objectid = od.objectid
         LEFT JOIN *PREFIX*hr_jobseeker AS ohj ON ohj.id = ie.jobseekerid
         LEFT JOIN *PREFIX*calendar_interview_result ir ON ir.jobseekerid = ie.jobseekerid
         LEFT JOIN #__jobs_job jj ON od.jobid = jj.id
         WHERE jj.employer_id = ".intval($master_uid)." AND o.id = ".intval($object_id);
      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $pre_info = $cache->call(array($this, '_getEventPreviewInfo'), $query);
      // Event Driven Cache/Object Caching
      CacheHelper::setTriggers(PFactory::getMasterUser()->get("id"), $cache, array('*PREFIX*calendar_objects-'.$object_id, '*PREFIX*calendar_interview_events-'.$object_id,'*PREFIX*calendar_objects_details-'.$object_id,'#__jobs_job'));

      if ( empty($pre_info) ) {
         $this->setError("没有找到相关安排！");
         return false;
      }

      // Get date & time with right timezone
      $real_startdate = PFactory::getDate($pre_info['startdate']);

      unset($pre_info['startdate']);
      $pre_info['title_date'] = JHtml::_('date', $real_startdate->toUnix(), 'Y-m-d') . " " .$real_startdate->dayToString(JHtml::_('date', $real_startdate->toUnix(), 'w'), true);

      // Get interviewers
      if ( $pre_info && $pre_info['objecttype'] == 'VEVENT' ) {
         $interviewers = $this->getEventInterviewers($object_id,$master_uid);
         if ( !empty($interviewers) ) {
            $interviewer_array = array();

            foreach ($interviewers as $k => $v) {
               if (!empty($v)) {
                  $start_time = JHtml::_('date', $v->startdate, 'H:i');
                  $end_time = JHtml::_('date', $v->enddate, 'H:i');

                  $interviewer_array[] = array(
                     'show_name' => $v->show_name,
                     'email' => $v->email,
                     'token' => $v->token,
                     'start_time' => $start_time,
                     'end_time' => $end_time,
                     'suggestion' => $v->suggestion,
                     'suggestion_id' => $v->suggestion_id,
                     'objectid' => $v->objectid
                  );
               }
            }
            $pre_info['interviewers'] = $interviewer_array;
         }
      } else {
         // This is a VTODO
         $pre_info['entry_deaddate'] = JHtml::_('date', $real_startdate->toUnix(), 'Y-m-d');
         $pre_info['entry_deadline'] = JHtml::_('date', $real_startdate->toUnix(), 'H:i');
      }

      return $pre_info;
   }

   public function _getEventPreviewInfo($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssoc();
   }

   /**
    * Notify Jobseeker via email
    * @param  int $objectid master object id
    * @param  string $type  add or edit
    * @return bool
    */
   public function notifyJobseeker($objectid, $type=null,$sendsms = 'sms') {
      $db = PFactory::getDbo();
      $master_uid = PFactory::getMasterUser()->get("id");
      $query = "SELECT g.email,g.source,g.company_id,g.name,g.phone as mobile,f.comp_name, c.jobseekerid, c.jobseekername, e.job_title, b.notify_text, b.contact, b.contact_phone, b.address, b.is_phone, a.startdate, h.token,oa.file_name,b.attachment,IF(jus.email IS NOT NULL, jus.email, c.uid) AS company_email
         FROM *PREFIX*calendar_objects a
         LEFT JOIN *PREFIX*calendar_objects_details b ON b.objectid = a.id
         LEFT JOIN *PREFIX*calendar_interview_events c ON c.objectid = a.id
         LEFT JOIN #__users jus ON jus.id = c.uid
         LEFT JOIN #__jobs_job e ON e.id = b.jobid
         LEFT JOIN #__jobs_employer f ON f.user_id = e.employer_id
         LEFT JOIN *PREFIX*hr_jobseeker g ON g.id = c.jobseekerid
         LEFT JOIN *PREFIX*calendar_interview_result h ON h.jobseekerid = g.id
         LEFT JOIN *PREFIX*calendar_offer_attachment oa ON b.attachment = oa.id
         WHERE e.employer_id = ".intval($master_uid)." AND a.id = ".intval($objectid);
      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $user = $cache->call(array($this, '_notifyJobseeker'), $query);
      // Event Driven Cache/Object Caching
      if ($user) {
         CacheHelper::setTriggers(PFactory::getMasterUser()->get("id"), $cache, array('*PREFIX*calendar_objects-'.$objectid, '*PREFIX*calendar_interview_events-'.$objectid, '*PREFIX*calendar_objects_details-'.$objectid, '*PREFIX*calendar_interview_result-'.$objectid, '*PREFIX*hr_jobseeker-'.$user['jobseekerid'], '#__jobs_job', '#__jobs_employer'));
      } else {
         // Don't cache null data
         $cache->removeCache(array($this, '_notifyJobseeker'), $query);
      }

      PCalendarHelper::sendNotifyEmail($user,$type);
      //first invite jobseeker interview,check if need send sms message.     
      $setting_result=PCompanyHelper::getUserQuota($user['company_id']);
      $cuser=PFactory::getUser();
      if(!empty($sendsms) && empty($cuser->testuser) && $setting_result['sms_left']>0){
         $user['startdate']=JHtml::_('date', $user['startdate'], 'Y-m-d H:i');
         PCalendarHelper::sendSMSMessage($user,'offlineinterview_0d'); 
      }
      return true;
   }

   public function _notifyJobseeker($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssoc();
   }

   /**
    * A function to check login user has or hasnot got the permission to edit one object(inc vevent and vtodo)
    * @param  int $objectid object ID ( Could be master objectid or sub objectid )
    * @return bool
    */
   public function checkObjectEditPermission($objectid) {
      $db = PFactory::getDbo();
      $master_uid = PFactory::getMasterUser()->get("id");

      if ( PFactory::isMasterUser() ) {
         // Check is objectid belongs to his company
         $query = "SELECT COUNT(*)
            FROM *PREFIX*calendar_interview_events ie
            LEFT JOIN *PREFIX*calendar_objects_details od ON od.objectid = ie.parent
            LEFT JOIN #__jobs_job jj ON jj.id = od.jobid
            WHERE ie.objectid = ".intval($objectid)." AND jj.employer_id = ".intval($master_uid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $count = $db->loadResult();

         return empty($count) ? false : true;
      } else {
         $userid = OC_USER::getUser();
         if ( !$userid ) {
            return false;
         }
         // Check edit permission
         $query = "SELECT hj.jobid
            FROM *PREFIX*hr_jobmanager hj
            LEFT JOIN *PREFIX*users_extended ue ON ue.uid = hj.manager
            LEFT JOIN *PREFIX*calendar_objects_details od ON od.jobid = hj.jobid
            LEFT JOIN *PREFIX*calendar_interview_events ie ON ie.parent = od.objectid
            LEFT JOIN #__jobs_job jj ON jj.id = od.jobid
            WHERE ie.objectid = ".intval($objectid)." AND ue.block=0 AND ue.deleted=0 AND ue.uid = ".$db->quote($userid)." AND jj.employer_id = ".intval($master_uid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $jobid = $db->loadResult();

         return empty($jobid) ? false : true;
      }

      return false;
   }

   /**
    * @brief Adds an object
    * @param integer $id Calendar id
    * @param string $data  object
    * @return bool
    */
   public function sendOffer($id,$data,$post) {
      if(!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录！');
         return false;
      }
      $db = PFactory::getDbo();

      $type = JRequest::getString('objecttype', '');

      $textval = JRequest::getString('textval', '');
      $entrysalary = JRequest::getInt('entrysalary', 0);
      $salary = JRequest::getInt('salary', 0);
      $entryenddate = JRequest::getString('entryenddate', '');
      
      $attachment = JRequest::getInt('attachmentid', 0);
      $copywith = JRequest::getString('copywith', '');
      $from = JRequest::getString('from', '');
      $fromtime = JRequest::getString('fromtime', '');
      $startdate = $from.' '.$fromtime;
      $enddate =   $from.' '.'23:59:59';
      $interviewobjectid = JRequest::getInt('objectid', 0);
      $sendtype = JRequest::getString('type','');

      $jobseekerid = JRequest::getInt('jobseekerid', 0);
      $jobid = JRequest::getInt('jobid', 0);
      $jobseekername = JRequest::getString('jobseekername', '');
      $department = JRequest::getString('department','');
      $sendsms = JRequest::getString('sendsms','');

      if(empty($from) || empty($fromtime)){
         $this->setError('必填信息不能为空！');
         return false;
      }

      if(!PCompanyHelper::checkJobManagerPermissions($jobid)){
         $this->setError('您沒有操作权限！');
         return false;
      }

      if(!$this->checkDate($from)) {
         $this->setError("入职日期格式有误！");
         return false;
      }

      if(!empty($entryenddate)){
         if(!$this->checkDate($entryenddate)) {
            $this->setError("试用期至日期格式有误！");
            return false;
         }
      }
      
      if( !$this->checkTime(urldecode($fromtime)) ) {
        $this->setError("入职时间格式有误！");
        return false;
      }
      
      $config = PFactory::getConfig();
      $startdate = PFactory::getDate($startdate, $config->get("offset"));
      $now = PFactory::getDate();

      if($startdate->toUnix() <= $now->toUnix()){
         $this->setError("入职时间必须大于现在时间！");
         return false;
      }

      if(!PCalendarHelper::checkSendOfferDate($startdate,$jobseekerid)){
         $this->setError("入职时间必须大于最后一轮面试时间！");
         return false;
      }
      
      $object = OC_VObject::parse($data);
      list($objecttype,$startdate,$enddate,$summary,$repeating,$uid) = $this->extractData($object);

      if(is_null($uid)) {
         $object->setUID();
         $data = $object->serialize();
      }

      $uri = 'Pipahr-'.md5($data.rand().time()).'.ics';

      $db = PFactory::getDbo();
      $query = "INSERT INTO *PREFIX*calendar_objects (`calendarid`,`objecttype`,`startdate`,`enddate`,`repeating`,`summary`,`calendardata`,`uri`,`lastmodified`) VALUES (".intval($id).",".$db->quote($type).",".$db->quote($startdate).",".$db->quote($enddate).",".intval($repeating).",".$db->quote($summary).",'".$data."',".$db->quote($uri).",".time().")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError('发送失败！');
         return false;
      } else {
         $objectid = $db->insertid();
      }

      // SET resume status 310:已发送offer
      if(!PResumesHelper::setResumeStatus($jobseekerid,PCalendarHelper::JOBSEEKER_STATUS_SENDED)){
         return false;
         $this->setError('发送失败！');
      }

      $master_uid = PFactory::getMasterUser()->get("id");
      if (!empty($objectid)) {
         if(!empty($copywith)){
            $copywitharray = array();
            $copywitharray = array_unique(explode(',', str_replace(' ', '', $copywith)));

            for($i=0;$i<count($copywitharray);$i++){
               if(!JMailHelper::isEmailAddress($copywitharray[$i])) {
                  $this->setError("不合法的邮箱:".$copywitharray[$i]);
                  return false;
               }
            }

            $copylist = array();
            foreach($copywitharray as $v){
               array_push($copylist, "(".intval($master_uid).",".intval($objectid).",".$db->quote($v).",UTC_TIMESTAMP)");
            }

            $query = "INSERT INTO *PREFIX*calendar_offer_carboncopy (`uid`,`objectid`,`copywith`,`copytime`) VALUES ". implode(",", $copylist);
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if (!$db->execute()) {
               $this->setError('发送失败！');
               return false;
            }
         }
         $query = "INSERT INTO *PREFIX*calendar_objects_details (`objectid`,`jobid`) VALUES(".intval($objectid).",".intval($jobid).")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();

         $query = "INSERT INTO *PREFIX*calendar_interview_events (`parent`,`objectid`,`eventtype`,`jobid`,`uid`,`displayname`,`jobseekerid`,`jobseekername`) VALUES (".intval($objectid).",".intval($objectid).",".$db->quote($type).",".intval($jobid).",".$db->quote(OC_USER::getUser()).",".$db->quote(OC_USER::getDisplayName()).",".intval($jobseekerid).",".$db->quote($jobseekername).")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();

         $query = "INSERT INTO *PREFIX*calendar_offer (`objectid`,`jobseekerid`,`salary`,`entry_deadline`,`offer_text`,`department`,`attachment`,`entry_enddate`,`entrysalary`,`created`) VALUES(".intval($objectid).",".intval($jobseekerid).",".intval($salary).",".$db->quote($startdate).",".$db->quote($textval).",".$db->quote($department).",".intval($attachment).",".$db->quote($entryenddate).",".intval($entrysalary).",UTC_TIMESTAMP)";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();

         $query = "UPDATE *PREFIX*calendar_interview_result SET last_offer_objectid = ".intval($objectid)." WHERE last_interview_objectid = ".intval($interviewobjectid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
      }
      if (!$db->execute()) {
         $this->setError('发送失败！');
         return false;
      } else {
         if($sendtype == 1){
            $masteruser = PFactory::isMasterUser();
            // Get Email data
            $query = "SELECT e.source as jobsource, g.email,g.source,g.company_id,g.name,g.phone as mobile,f.comp_name, c.jobseekerid, c.jobseekername, e.job_title, d.entry_deadline, d.salary, d.offer_text, GROUP_CONCAT(cc.copywith) AS copywith, ca.file_name, d.entry_enddate, d.department, d.entrysalary, d.attachment,IF(jus.email IS NOT NULL, jus.email, c.uid) AS company_email
            FROM *PREFIX*calendar_objects a
            LEFT JOIN *PREFIX*calendar_objects_details b ON b.objectid = a.id
            LEFT JOIN *PREFIX*calendar_interview_events c ON c.objectid = a.id".
            ($masteruser ? ' LEFT JOIN #__users jus ON jus.id = c.uid' : ' LEFT JOIN *PREFIX*users_extended AS oue ON oue.uid = c.uid  LEFT JOIN #__users AS jus ON jus.id = oue.owner ')
            ." LEFT JOIN *PREFIX*calendar_offer d ON d.objectid = a.id
            LEFT JOIN *PREFIX*calendar_offer_attachment ca ON ca.id = d.attachment
            LEFT JOIN *PREFIX*calendar_offer_carboncopy cc ON cc.objectid =d.objectid
            LEFT JOIN #__jobs_job e ON e.id = b.jobid
            LEFT JOIN #__jobs_employer f ON f.user_id = e.employer_id
            LEFT JOIN *PREFIX*hr_jobseeker g ON g.id = c.jobseekerid
            WHERE a.id = ".intval($objectid);
            $db->setQuery(PUtil::replaceOCPrefix($db->useLastConnection($query)));
            $user = $db->loadAssoc();  
            if(!empty($user)){
               $offertoken = OCP\Share::shareItem('offer', $objectid, OCP\Share::SHARE_TYPE_LINK, $user['email'], \OCP\PERMISSION_READ);
               $user['token'] = $offertoken;
            } 
            
            // Send jobseeker offer
            PCalendarHelper::sendOfferEmail($user);
            //Send copywith offer
            if(!empty($copywitharray)){
               foreach($copywitharray as $key=>$val){
                  $user['copywithemail'] = $val;
                  PCalendarHelper::sendOfferEmail($user,'copywith');
               }
            }
            // Check send smsmessage
            $setting_result=PCompanyHelper::getUserQuota($user['company_id']);
            $cuser=PFactory::getUser();
            if(!empty($sendsms) && empty($cuser->testuser) && $setting_result['sms_left']>0) {
               $user['entry_deadline']=JHtml::_('date', $user['entry_deadline'], 'Y-m-d H:i');
               PCalendarHelper::sendSMSMessage($user, 'entry_invite_0d');
            }
         }
      }

      // update num events created 
      PCalendarHelper::updateNumEvent();

      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects', '*PREFIX*calendar_interview_events', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result', '*PREFIX*calendar_offer'));

      return true;
   }

   /**
    * This is a function to check the startdate is changed or not when update the event
    * @param  int    $id    Objectid
    * @param  string $data  Post data in vcal format
    * @return bool          The date is changed?
    */
   public function checkDateChange($id, $data) {
      $db = PFactory::getDbo();
      $query = "SELECT startdate FROM *PREFIX*calendar_objects WHERE id = ".intval($id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $old_startdate = $db->loadResult();

      $object = OC_VObject::parse($data);
      list($objecttype,$startdate,$enddate,$summary,$repeating,$uid) = $this->extractData($object);

      $old_startdate = PFactory::getDate($old_startdate);
      $new_startdate = PFactory::getDate($startdate);
      if ( $old_startdate == $new_startdate ) {
         return false;
      } else {
         return true;
      }
   }

   public function checkJobseekerDataChange($objectid, $post, &$summaryChanged = false) {
      if(empty($objectid))
         return false;
      $db = PFactory::getDbo();
      $query = "SELECT address, contact_phone, contact, notify_text, summary FROM *PREFIX*calendar_objects_details WHERE objectid = ".intval($objectid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadAssoc();
      if(empty($result)) return false;
      if($result['summary'] != $post['summary']){
         $summaryChanged = true;
      }
      if (($result['address'] != $post['address']) 
        || ($result['contact_phone'] != $post['contact_phone']) 
        || ($result['contact'] != $post['contact']) 
        || ($result['notify_text'] != $post['notify_text'])) {
        return true;
      }else{
        return false;
      }
   }

   /**
    * Check reject url is valid
    * @param  string $token User token saved in oc_calendar_interview_result
    * @param  string $email Jobseeker's email
    * @return bool
    */
   public function checkRejectToken($token='', $email='') {
      $db = PFactory::getDbo();
      $query = "SELECT rejected,canceled
         FROM *PREFIX*calendar_interview_result a
         LEFT JOIN *PREFIX*hr_jobseeker b ON b.id = a.jobseekerid
         WHERE a.token = ".$db->quote($token)." AND b.email = ".$db->quote($email);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $cancelemailstatus = $db->loadAssoc();

      if ( is_numeric($cancelemailstatus['rejected']) && is_numeric($cancelemailstatus['canceled'])) {
         if ( $cancelemailstatus['rejected'] == PCalendarHelper::INTERVIEW_REJECT ) {
            $this->setError('您已成功拒绝了此面试！');
            return false;
         }elseif ( $cancelemailstatus['canceled'] == PCalendarHelper::INTERVIEW_CANCEL ){
            $this->setError('您的面试已经被取消！');
            return false;
         }
      } else {
         $this->setError('没有找到相关信息。');
         return false;
      }

      return true;
   }

   /**
    * Get basic info for reject page view
    * @param  string $token User token saved in oc_calendar_interview_result
    * @param  string $email Jobseeker's email
    * @return object
    */
   public function getBasicRejectPageInfo($token, $email) {
      $db = PFactory::getDbo();
      $query = "SELECT b.name, d.comp_name, c.job_title, a.jobseekerid, a.jobid, c.employer_id AS companyid, a.last_interview_objectid AS objectid, co.startdate
         FROM *PREFIX*calendar_interview_result a
         LEFT JOIN *PREFIX*calendar_interview_events cie ON cie.objectid = a.last_interview_objectid
         LEFT JOIN *PREFIX*calendar_objects co ON cie.parent = co.id
         LEFT JOIN *PREFIX*hr_jobseeker b ON b.id = a.jobseekerid
         LEFT JOIN #__jobs_job c ON c.id = a.jobid
         LEFT JOIN #__jobs_employer d ON d.user_id = c.employer_id
         WHERE a.token = ".$db->quote($token)." AND b.email = ".$db->quote($email);
      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getBasicRejectPageInfo'), $query);
      // Event Driven Cache/Object Caching
      if ($result) {
         CacheHelper::setTriggers(PFactory::getMasterUser()->get("id"), $cache, array('*PREFIX*calendar_interview_result-'.$result->objectid, '#__jobs_job', '#__jobs_employer'));
      } else {
         // Don't cache null data
         $cache->removeCache(array($this, '_getBasicRejectPageInfo'), $query);
      }

      return $result;
   }

   public function _getBasicRejectPageInfo($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   /**
    * Function to save rejected status to database and send notify email to interviewers
    * @return bool
    */
   public function reject() {
      if( !PSession::checkToken('post') ) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }

      $jobseekerid = JRequest::getInt('jobseekerid', 0);
      $jobid = JRequest::getInt('jobid', 0);
      $companyid = JRequest::getInt('companyid', 0);
      $reason = JRequest::getString('reason', '');
      $from = JRequest::getString('from','');
      $fromtime = JRequest::getString('fromtime','');
      $newinterviewtime = $from.' '.$fromtime;

      if ( empty($jobseekerid) || empty($jobid) || empty($companyid) ) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }

      if ( empty($reason) ) {
         $this->setError('请选择放弃原因！');
         return false;
      }

      if ( $reason == '希望更改其他时间' && empty($newinterviewtime) ){
         $this->setError('请输入要更改的时间！');
         return false;
      }

      $db = PFactory::getDbo();
      // Update oc_calendar_interview_result rejected field
      $query = "UPDATE *PREFIX*calendar_interview_result SET rejected = ".intval(PCalendarHelper::INTERVIEW_REJECT).", reject_time = UTC_TIMESTAMP WHERE jobseekerid = ".intval($jobseekerid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if ( !$db->execute() ) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }

      // Update jobseeker's status in oc_hr_jobseeker table
      if ( !PResumesHelper::setResumeStatus($jobseekerid,PCalendarHelper::JOBSEEKER_STATUS_CANCEL) ) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }

      require_once(OC_App::getAppPath('interview') . "/lib/interview.php");
      $interview_model = new PInterviewModelInterview();
      $interview_model->insertTags($reason, $companyid, $jobseekerid, self::$validRejectReasons);

      // Notify interviewers event has been canceled
      $query = "SELECT last_interview_objectid FROM *PREFIX*calendar_interview_result WHERE jobseekerid = ".intval($jobseekerid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $parent_id = $db->loadResult();
      if ( !empty($parent_id) ) {
         // Update interview result in oc_calendar_objects_details table
         $query ="UPDATE *PREFIX*calendar_objects_details SET result=".intval(PCalendarHelper::INTERVIEW_RESULT_CANCEL)." WHERE objectid= ".intval($parent_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError('网络超时，请刷新页面！');
            return false;
         }

         $query = "SELECT canceled FROM *PREFIX*calendar_interview_result WHERE jobseekerid= ".intval($jobseekerid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $interviewerstatus = $db->loadResult();

         if(is_numeric($interviewerstatus) && $reason != '希望更改其他时间'){
            if($interviewerstatus != PCalendarHelper::INTERVIEW_CANCEL){
               $interviewers = PCalendarHelper::getInterviewerEmailDataFromParentid($parent_id);
               foreach ($interviewers as $k => $user) {
                  PCalendarHelper::sendInterviewCancelEmail($user);
                }
            }
         }
         $query = "SELECT cie.jobseekername,co.startdate,co.enddate,jj.source as jobsource,jj.job_title,group_concat(IF(u.email IS NOT NULL, u.email, cie.uid)) as email,cod.is_phone,cie.jobid,cie.jobseekerid,ohj.source
                   FROM *PREFIX*calendar_interview_events cie
                   LEFT JOIN *PREFIX*calendar_objects_details cod ON cie.parent = cod.objectid
                   LEFT JOIN *PREFIX*calendar_objects co ON co.id = cie.objectid
                   LEFT JOIN #__jobs_job jj ON jj.id = cie.jobid
                   LEFT JOIN #__users u ON u.id = cie.uid
                   LEFT JOIN *PREFIX*hr_jobseeker AS ohj ON ohj.id = cie.jobseekerid
                   WHERE cie.objectid = ".intval($parent_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $interviewcreater = $db->loadAssoc();
         $interviewcreater['reason'] = $reason.($reason == '希望更改其他时间'?$newinterviewtime:'');
         PCalendarHelper::sendInterviewCancelEmail($interviewcreater,'creater');
      }
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details-'.$parent_id, '*PREFIX*calendar_interview_result-'.$parent_id, '*PREFIX*calendar_offer-'.$parent_id));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result', '*PREFIX*calendar_offer'));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*hr_jobseeker'));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*hr_jobseeker-'.intval($jobseekerid)));

      return true;

   }
   
   public function rejectoffer(){
      if(!PSession::checkToken('post')) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }
      $token = JRequest::getString('token','');
      $share_with = JRequest::getString('sharewith','');
      if(empty($token ) || empty($share_with)) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }
      $data = $this->getShareOfferData($token,$share_with);
      if(empty($data)){
         $this->setError('网络超时，请刷新页面！');
         return false;
      }

      $db = PFactory::getDbo();
      $query = "SELECT last_interview_objectid FROM *PREFIX*calendar_interview_result WHERE jobseekerid = ".intval($data['jobseekerid']);
      $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
      $lastinterviewobjectid = $db->loadResult();
      if(empty($lastinterviewobjectid)){
         $this->setError('网络超时，请刷新页面！');
         return false;
      }      
      $query = "UPDATE *PREFIX*calendar_objects_details SET entry_stat = 0, offer_stat = ".PCalendarHelper::OFFER_FEEDBACKSTATUS_REJECTED." WHERE  objectid IN(".intval($data['objectid']).",".intval($lastinterviewobjectid).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }
      if(!PResumesHelper::setResumeStatus($data['jobseekerid'],PCalendarHelper::JOBSEEKER_STATUS_REJECTED)) {
         $this->setError('网络超时，请刷新页面！');
         return false;
      }
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*calendar_objects_details-'.$data['objectid']));
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*calendar_objects_details'));
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*hr_jobseeker-'.$data['jobseekerid']));
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*hr_jobseeker'));
      return true;
   }

   public function acceptoffer($data,&$error){
      $db = PFactory::getDbo();
      $query = "SELECT last_interview_objectid FROM *PREFIX*calendar_interview_result WHERE jobseekerid = ".intval($data['jobseekerid']);
      $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
      $lastinterviewobjectid = $db->loadResult();
      if(empty($lastinterviewobjectid)){
         $error='网络超时，请刷新页面！';
         return false;
      }      
      $query = "UPDATE *PREFIX*calendar_objects_details SET entry_stat=".PCalendarHelper::ENTRY_FEEDBACKSTATUS_NOENTRY.", offer_stat = ".PCalendarHelper::OFFER_FEEDBACKSTATUS_ACCEPTED." WHERE  objectid IN(".intval($data['objectid']).",".intval($lastinterviewobjectid).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         $error='网络超时，请刷新页面！';
         return false;
      }
      if(!PResumesHelper::setResumeStatus($data['jobseekerid'],PCalendarHelper::JOBSEEKER_STATUS_ACCEPTED)) {
         $error='网络超时，请刷新页面！';
         return false;
      }
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*calendar_objects_details-'.$data['objectid']));
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*calendar_objects_details'));
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*hr_jobseeker-'.$data['jobseekerid']));
      CacheHelper::triggerRemove($data['userid'], array('*PREFIX*hr_jobseeker'));
      return true;
   }
   /**
    * Check if current user has permission to edit or add an event or todo
    * @param  array $data Some data we need
    * @return bool
    */
   public function checkPermissions($data) {
      $db = PFactory::getDbo();
      $objecttype = isset($data['objecttype']) ? $data['objecttype'] : 'VEVENT';
      $master_uid = PFactory::getMasterUser()->get("id");

      // Check if jobseeker resume is perm deleted AND is given jobid and jobtitle belong to the right company id.
      $query = "SELECT COUNT(*)
         FROM *PREFIX*hr_jobseeker hj
         LEFT JOIN #__jobs_job jj ON jj.id = hj.job_id
         WHERE hj.id = ".intval($data['jobseekerid'])." AND hj.company_id = ".intval($master_uid)." AND hj.job_id = ".intval($data['jobid'])." AND jj.job_title = ".$db->quote($data['jobtitle']);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if ( $db->loadResult() <= 0 ) {
         if ( $objecttype == 'VEVENT' ) {
            $this->setError("该应聘者的简历已被永久删除，无法新建或修改面试日程");
         } elseif ( $objecttype == 'VTODO' ) {
            $this->setError("该应聘者的简历已被永久删除，无法新建或修改入职日程");
         }
         return false;
      }

      return true;
   }

   public function checkDate($date, $format = 'Y-m-d'){
      $dt = DateTime::createFromFormat($format, $date);
      return $dt && $dt->format($format) == $date;
   }

   public function getShareOfferData($token,$sharewith){
      $db =  PFactory::getDbo();
      $query = "SELECT a.id as objectid, b.offer_stat, c.jobseekerid, g.name as jobseekername, se.uid_owner as userid, se.token, se.share_with,ju.name as company_name
            FROM *PREFIX*calendar_objects a
            LEFT JOIN *PREFIX*calendar_objects_details b ON b.objectid = a.id
            LEFT JOIN *PREFIX*calendar_interview_events c ON c.objectid = a.id
            LEFT JOIN *PREFIX*hr_jobseeker g ON g.id = c.jobseekerid
            LEFT JOIN *PREFIX*share se ON se.item_source = c.objectid
            LEFT JOIN #__users as ju ON ju.id = g.company_id
            WHERE se.token= ".$db->quote($token).' AND se.share_with = '.$db->quote($sharewith);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssoc();
   }
}
